Sql-server – Get permissions in fixed or user-defined server roles in SQL Server 2012

database-administrationpermissionssql serversql-server-2012

I'd like to know how to get a list of permissions that belong to fixed and/or user-defined server roles in SQL Server 2012.

In previous versions there was an sproc sp_srvrolepermission but this is deprecated in 2012 and it returns only permissions for fixed server roles.

Update

I have run the following query and get the result down below. I was hoping this query might give me the desired result. However, although it does give me results for user-defined roles (MyTestRole below), the fixed roles are nowhere to be found.

SELECT pri.name principal_name, per.class_desc, per.permission_name
FROM sys.server_permissions per
JOIN sys.server_principals pri ON per.grantee_principal_id = pri.principal_id
ORDER BY pri.name

Result:

##MS_AgentSigningCertificate##            SERVER    CONNECT SQL
##MS_PolicyEventProcessingLogin##         SERVER    CONNECT SQL
##MS_PolicySigningCertificate##           SERVER    CONTROL SERVER
##MS_PolicySigningCertificate##           SERVER    VIEW ANY DEFINITION
##MS_PolicyTsqlExecutionLogin##           SERVER    CONNECT SQL
##MS_PolicyTsqlExecutionLogin##           SERVER    VIEW ANY DEFINITION
##MS_PolicyTsqlExecutionLogin##           SERVER    VIEW SERVER STATE
##MS_SmoExtendedSigningCertificate##      SERVER    VIEW ANY DEFINITION
##MS_SQLAuthenticatorCertificate##        SERVER    AUTHENTICATE SERVER
##MS_SQLEnableSystemAssemblyLoadingUser## SERVER    CONNECT SQL
##MS_SQLEnableSystemAssemblyLoadingUser## SERVER    UNSAFE ASSEMBLY
##MS_SQLReplicationSigningCertificate##   SERVER    AUTHENTICATE SERVER
##MS_SQLReplicationSigningCertificate##   SERVER    VIEW ANY DEFINITION
##MS_SQLReplicationSigningCertificate##   SERVER    VIEW SERVER STATE
##MS_SQLResourceSigningCertificate##      SERVER    VIEW ANY DEFINITION
##MS_SSISServerCleanupJobLogin##          SERVER    CONNECT SQL
MyTestRole                                SERVER    VIEW ANY DATABASE
NT AUTHORITY\SYSTEM                       SERVER    ALTER ANY AVAILABILITY GROUP
NT AUTHORITY\SYSTEM                       SERVER    CONNECT SQL
NT AUTHORITY\SYSTEM                       SERVER    VIEW SERVER STATE
NT Service\MSSQLSERVER                    SERVER    CONNECT SQL
NT SERVICE\ReportServer                   SERVER    CONNECT SQL
NT SERVICE\SQLSERVERAGENT                 SERVER    CONNECT SQL
NT SERVICE\SQLWriter                      SERVER    CONNECT SQL
NT SERVICE\Winmgmt                        SERVER    CONNECT SQL
public                                    ENDPOINT  CONNECT
public                                    ENDPOINT  CONNECT
public                                    ENDPOINT  CONNECT
public                                    ENDPOINT  CONNECT
public                                    SERVER    VIEW ANY DATABASE
<computer>\<username>                     SERVER    CONNECT SQL
sa                                        SERVER    CONNECT SQL

Best Answer

Take a look at the sys.server_permissions and sys.database_permissions. In both cases, the grantee_principal_id will be the principal_id of the role (found in sys.server_principals or sys.database_principals, respectively). Keep in mind that you may also need to troll through role memberships for the role (i.e. in a database, I can create a role called "MyRole" that is a member of the db_datareader built-in role).

Related Topic