SQL Server – Database – Permissions

Background

As a follow-up to our discussion on SQL Server Instance Security permissions, let us discuss database permissions.

BTW, our discussion on SQL Server Instance Security permission is here.

Delegating Permissions

Permissions can be granted via adding principals to database roles.

Permissions can also be granted via assigning permissions.

Roles

View Available Roles

sp_helprole

To view available roles, please issue sp_helprole.

Syntax


exec sp_helprole

Output

sp_helprole

Explanation

  1. Public
    • All database principals belong to the public role
  2. Pre-defined roles
    • Pre-defined roles are MSFT’s pre-tailored roles
    • The RoleId starts at 16384
    • Roles
      • *deny*
        • db_denydatareader
          • Be careful with db_denydatareader as it means that those members granted membership will not be able to read data
        • db_denydatawriter
          • Also, be careful with db_denydatawriter as it means member-ed will not be able to write data
  3. User Defined Roles
    • Reporting Services
      • RSExecRole

Review Role Members

Outline

  1. sp_helprolemember
  2. sp_helpuser

sp_helprolemember

Overview

We can utilize sp_helprolemember to read role members.

Syntax


exec sp_helprolemember
     @rolename = [principal]

Sample


declare @rolename sysname

set @rolename = 'RSExecRole';

exec sp_helprolemember
       @rolename = @rolename

Output

sp_helprolemember__20180623_0846PM

sp_helpuser

Overview

When applied against a role, the sp_helpuser function returns information about role members.

Syntax


exec sp_helpuser
     @name_in_db = [principal]

Sample


use [ReportServer]
go

declare @rolename sysname

set @rolename = 'RSExecRole';

exec sp_helpuser
	@name_in_db = @rolename

Output

sp_helpuser__20180623_0912PM

Explanation

  1. Role RSExecRole has a single member NT SERVICE\ReportServer

sys.database_role_members

Overview

The sys.database_role_members table list roles and corresponding members.

Sample


use [ReportServer]
go

declare @rolename sysname

set @rolename = 'RSExecRole'

select
		  [role]
			= tblSDPR2.[name]

		, [member]
			= tblSDPR.[name]

from sys.database_role_members tblSDRM

-- cross reference members
inner join sys.database_principals tblSDPR

	on tblSDRM.member_principal_id = tblSDPR.principal_id 

-- cross reference roles
inner join sys.database_principals tblSDPR2
	on tblSDRM.role_principal_id = tblSDPR2.principal_id

where tblSDPR2.[name]
		= isNull
			(
				  @rolename
				, tblSDPR2.[name]
			)

Output

sysDOTdatabase_role_members__20180624_0940AM

Explanation

  1. Role RSExecRole has a single member NT SERVICE\ReportServer

Permissions

View Available Permissions

sys.fn_builtin_permissions

To view available permissions, please query sys.fn_builtin_permissions.

Be sure to pass along ‘database’ for the securable class you are querying for.

Sample


select 

		  tblSFNBP.class_desc
		, tblSFNBP.permission_name
		, tblSFNBP.type
		, tblSFNBP.covering_permission_name

from   sys.fn_builtin_permissions('Database') tblSFNBP

order by
	  tblSFNBP.class_desc
	, tblSFNBP.permission_name
	, tblSFNBP.type
	, tblSFNBP.covering_permission_name

Output

sysDOTfn_builtin_permissions__2018024__1002AM

View Assigned Permissions

Outline

  1. sys.database_permissions
  2. sp_helprotect

sys.database_permissions

To view assigned permissions, please query sys.database_permissions.

Sample


declare @principal sysname

set @principal = 'ivrusr'

select 

		  [principal]
			= tblSDPR.[name] 

		, [class]
			= tblSDPE.class

		, [classDescription]
			= tblSDPE.class_desc

		, [object]
			= case

				when (tblSDPE.class = 0)
					then
						  quotename
						  (
							db_name
							(
								tblSDPE.major_id
							)
						  )

				when (tblSDPE.class = 1)
					then
						  quotename
						  (
							object_schema_name
							(
								tblSDPE.major_id
							)
						  )
						+ '.'
						+ quotename
							(
								object_name
								(
									tblSDPE.major_id
								)
							)

				when (tblSDPE.class = 3)
					then
						  quotename
						  (
							schema_name
							(
								tblSDPE.major_id
							)
						 )

			  end

		, [permission]
			= tblSDPE.permission_name

		, [state]
			= tblSDPE.state_desc

-- database principals
from sys.database_principals tblSDPR

-- database permissions
inner join sys.database_permissions tblSDPE

		on tblSDPE.[grantee_principal_id] = tblSDPR.principal_id

where tblSDPR.name = isNull(@principal, tblSDPR.name)

/*
	Skip MS Shipped Objects - Begin
*/
and   not exists
	(

		select *

		from   sys.database_permissions tblSDP_I

		where 

			(

					( tblSDP_I.major_id = tblSDPE.major_id )

				and ( tblSDP_I.class = 1 )

				and (
						objectProperty
						(
								tblSDP_I.major_id
							, 'IsMSShipped'
						) = 1
					)
			)

	) -- not exists

/*
	Skip MS Shipped Objects - End
*/

order by

	  tblSDPR.[name]

	, tblSDPE.class

	, tblSDPE.class_desc

Output

sysDOTdatabase_permissions__20180624_1015AM

sp_helprotect

The classic API for determining permission set is sp_helprotect.

Arguments

Argument Usage Explanation Valid Values
name Object Name
username Principal
grantorname Grantor Name
permissionarea Principal Display object permissions (character string o), statement permissions (character string s), or both (os). With a default of os.
type can be any combination of o and s

Sample


declare @principal sysname
declare @permissionarea varchar(10)

set @permissionarea = 'os'
set @principal = 'ivrusr'

exec sp_helprotect
		  @name = null
		, @username = @principal
		, @grantorname = null
		, @permissionarea = @permissionarea

Output

sp_helprotect__20180624__1116AM

 

Reference

  1. Microsoft
    • Security Functions
      • sp_helprole
      • sp_helprotect
      • sys.fn_builtin_permissions
      • sp_helpuser
    • System Catalog Views
      • sys.database_role_members

 

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