0

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
rwwilden
  • 369
  • 1
  • 5
  • 13

1 Answers1

1

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).

Ben Thul
  • 3,024
  • 17
  • 24
  • Didn't realize that roles are principals too. Still missing a part of the picture though: `sysadmin`, for example, is nowhere to be found in `sys.server_permissions`, implying this role doesn't have any permissions. – rwwilden Apr 17 '13 at 18:08
  • sysadmin is a special "I can do anything" role. – Ben Thul Apr 17 '13 at 20:12
  • Ok, that's maybe not the best example. But the same is true for the other fixed server roles, they do not have permissions in `sys.server_permissions`. I updated my question with a query and its result, joining `sys.server_permissions` with `sys.server_principals`. – rwwilden Apr 18 '13 at 05:19
  • Apparently, fixed roles are "funny" and their permissions are likely hardcoded into the engine rather than using permissions like everything else. That said, they're all documented. Like here (http://msdn.microsoft.com/en-us/library/ms188659.aspx) and here (http://msdn.microsoft.com/en-us/library/ms189612%28v=sql.105%29.aspx). – Ben Thul Apr 18 '13 at 12:49
  • Thanks, I hadn't found the second link yet but it's a good overview of the permissions mapped to the fixed server roles. – rwwilden Apr 19 '13 at 05:15