SQL Server – Instance – Permissions

 

Background

Let us review SQL Instance Server Roles and Server Permissions.

Outline

  1. server roles
    • To get a read of available server roles, please issue sp_helpsrvrole
    • To read assigned server roles, please access sys.server_role_members
  2. server permissions
    • To get a read of available server permissions, please issue sys.fn_builtin_permissions
    • To identify server permissions for self, please use sys.fn_my_permissions
    • To gather assigned server permissions, please access sys.server_permissions

Server Roles

List available Server Roles

sp_helpsrvrole

Syntax


 exec sp_helpsrvrole

Output

serverRoles_20180622_0902AM

List Principals and Assigned Server Roles

sys.server_role_members

Syntax


select
          [principal]
            = tblSSP.[name]

/*

        , [principalID]
            = tblSSP.principal_id

        , [principalType]
            = tblSSP.[type_desc]

        , [principalCreateTS]
            = tblSSP.create_date

*/
        , [role]
            = tblSSR.[name]

/*

        , [roleID]
            = tblSSRM.[role_principal_id]
*/
from   sys.server_principals tblSSP

inner join sys.server_role_members tblSSRM

        on tblSSP.[principal_id] = tblSSRM.[member_principal_id]

inner join sys.server_principals tblSSR

        on tblSSRM.role_principal_id = tblSSR.principal_id

order by
      tblSSP.[name] asc
    , tblSSR.[name] asc

Output

sysDOTserver_role_Members_20180622_0932AM

Server Permissions

List Built-In Permissions

sys.fn_builtin_permissions

Syntax


select
		  [class]
			= tblSFBP.[class_desc]

		, [permision]
			= tblSFBP.[permission_name]

		, [type]
			= tblSFBP.[type]

from   sys.fn_builtin_permissions
		(
			'server'
		) tblSFBP

order by

	  tblSFBP.[class_desc]
	, tblSFBP.[permission_name]

Output

sysDOTfn_builtin_permissions__20180622_0951AM

List Server Permissions Assigned to Self

sys.fn_my_permissions

Syntax

select 

	  [principal]
		= SYSTEM_USER

	, [entity]
		= tblSFMP.[entity_name]

	, [permission]
		= tblSFMP.[permission_name]

from   sys.fn_my_permissions
			(
				  null
				, 'SERVER'
			) tblSFMP

order by
	  tblSFMP.[entity_name]
	, tblSFMP.[permission_name]

Output

sysDOTfn_my_permissions__20180622_1058AM.jpg

List Assigned Server Permissions

List principals and assigned permissions.

sys.server_permissions

Code


select
      [principal]
        = tblSSP2.[name]

    , [permission]
        = tblSSP.[permission_name]

    , [state]
        = tblSSP.[state_desc]

    --, tblSSP2.is_fixed_role

    --, tblSSP2.type
    -- , tblSSP.*

    , [principalType]
        = tblSSP2.[type_desc]

/*
    , [createTS]
        = tblSSP2.create_date
*/
from   sys.server_permissions tblSSP

INNER JOIN sys.server_principals tblSSP2

    ON tblSSP.grantee_principal_id
         = tblSSP2.principal_id

where
    (

        -- (tblSSP2.type != 'C')
        (
           tblSSP2.[type_desc]
             != 'CERTIFICATE_MAPPED_LOGIN'
        )

    )

and (
        (
            tblSSP.permission_name not in
                (
                    'CONNECT SQL'
                )
        )
    )

and (
        (
            -- class = 105
            tblSSP.class_desc not in
                (
                    'ENDPOINT'
                )
        )	

    )

order by
      tblSSP2.[name] asc
    , tblSSP.[permission_name] asc

Output

sysDOTserver_permissions__20180622_1037AM

 

References

  1. Microsoft
    • Docs / SQL / Relational databases / System catalog views
      • sys.server_permissions (Transact-SQL)
      • sys.server_role_members (Transact-SQL)
    • Docs / SQL / Relational databases / System stored procedures
      • sp_helpsrvrole (Transact-SQL)
    • Docs / SQL / Relational databases / System functions
      • sys.fn_builtin_permissions (Transact-SQL)
    • Docs / SQL / Relational databases / System stored procedures
      • Security Stored Procedures (Transact-SQL)

 

One thought on “SQL Server – Instance – Permissions

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s