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

 

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)

 

sp_helplogins in AWS/RDS

Background

Against AWS/RDS, reviewing SQL Server permissions, but unable to do via sp_helplogins.

sp_helplogins

Sample


declare @principal sysname

set @principal = 'dadeniji';

exec [dbo].[sp_helplogins]
     @LoginNamePattern = @principal

Error

Msg 15247, Level 16, State 1, Procedure sp_helplogins, Line 72 [Batch Start Line 8]
User does not have permission to perform this action.

Troubleshooting

Reviewed code and determined that it performs an explicit check to see if the running user has access to the securityadmin server role.


----------------  Only SA can run this  -------------------

if (not (is_srvrolemember('securityadmin') = 1))
begin

   raiserror(15247,-1,-1)

   select @RetCode = 1

   goto label_86

   return

<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>end

Remediation

Outline

  1. Schema/Name Change
  2. Comment out sysadmin check
  3. Comment out “set nocount on
  4. Place elsewhere as we can not use master

Code

Code – Schema/Name Change

The original module name is sys.sp_helplogins.

We are unable to use the sys schema and and also to avoid name collision with sp_helplogins, used dbo.sp_helplogins_Customized.

 

Code – Comment out sysadmin check


/*

--------------  Only SA can run this  -------------------

if (not (is_srvrolemember('securityadmin') = 1))
begin

    raiserror(15247,-1,-1)

    select @RetCode = 1
    goto label_86return

end

*/

Code – Comment out ‘set nocount on’

There are a couple of places where “set nocount on” is issued.

Commented both out, as well.

 

Code – Master

As we are unable to modify objects in the master db, please place else where.

 

Source Code Control

GitHub

DanielAdeniji/SQLServer.sp_helplogins
Link

References

  1. Microsoft
    • SQL Server
      • System Stored Procedures
        • sp_helplogins
          Link

 

 

Transact SQL :- Get Object Dependency Using sys.sql_expression_dependencies

 

Background

As a database Administrator, one has to quickly look at SQL and try to identify which objects it touches.

API

There are a couple of pathways to identify objects that a programmable object references.

Inclusive are:

  1. sp_depends
  2. sys.sql_expression_dependencies

Code

Sample

Procedure

SP – [school].[usp_TripParticipants_List]

Here is a SP that list those going on a trip…..


use [DBLab]
go

if object_id('school.usp_TripParticipants_List') is null
begin

    exec('create procedure [school].[usp_TripParticipants_List] as ')

end
go

alter procedure [school].[usp_TripParticipants_List]
(
    @tripID bigint
)
as

begin

    set nocount on

    set XACT_ABORT on

    ; with cteSchoolBody
    as
    (
        select
                 tblS.id
               , tblS.identifier
               , tblS.firstname
               , tblS.lastname
               , [pool] = 'Student'

        from   [school].[student] tblS

        union all

        select
                 tblF.id
               , tblF.identifier
               , tblF.firstname
               , tblF.lastname
               , [pool] = 'Faculty'

        from   [school].[faculty] tblF

    )
    select
              [tripID] = tblT.[id]

            , [tripName] = tblT.[name]

            , cteSB.[pool]

            , cteSB.lastname

            , cteSB.firstname

            , cteSB.identifier

    from   [school].[trip] tblT

    --inner join [school].[tripParticipant] tblTP
    inner join [tripParticipant] tblTP

        on tblT.id = tblTP.tripID

    inner join cteSchoolBody cteSB
        on tblTP.participantID = cteSB.id

    where (

            (
                tblT.[id]
                    = isNull(@tripID, tblT.[id])
            )

          )

    order by
              [tripName]

            , [pool]

            , cteSB.lastname

            , cteSB.firstname

end
go

metadata

Procedure

SP – [master].[dbo].[sp_ObjectDependants]

Here is a SP that we use to seek out dependants….


use [master]
go

if object_id('[dbo].[sp_ObjectDependants]') is null
begin

    exec('create procedure [dbo].[sp_ObjectDependants] as ')

end
go

alter procedure [dbo].[sp_ObjectDependants]
(
    @object sysname
)
as

begin

    ;with cteObjectDependencies
    as
    (

        -- find dependency
        -- exec sp_help 'sys.sql_expression_dependencies'
        SELECT
            [referencingObjectID]
            = referencing_id

        , [referencingObject]
            =   quoteName
                (
                    OBJECT_SCHEMA_NAME ( referencing_id )
                )
                + '.'
                + quoteName
                    (
                        OBJECT_NAME(referencing_id)
                    )	

        , [referencingObjectType]
            = obj.[type_desc]

        , [referecedObjectClass]
            = sed.referenced_class_desc

        , [referencedObjectID]
            = sed.referenced_id

        , [referencedObject]
            =
                isNull
                (
                    quoteName
                    (
                        sed.referenced_schema_name
                    )
                    + '.'
                , ''
                )
                + quoteName
                    (
                        sed.referenced_entity_name
                    )

            , [referencedObjectType]
                = objRef.[type_desc]

            , [isCallerDependent]
                = case
                    when (sed.is_caller_dependent=0) then 'N'
                    else 'Y'
                  end	

            , [isAmbiguous]
                = case
                    when (sed.is_ambiguous=0) then 'N'
                    else 'Y'
                  end	

        FROM sys.sql_expression_dependencies AS sed

        INNER JOIN sys.objects AS obj
            ON sed.referencing_id = obj.object_id

        LEFT OUTER JOIN sys.objects AS objRef
            ON sed.referenced_id = objRef.object_id	

        WHERE sed.referencing_id = object_id(@object)

    )
    select

           [referencedObject]

         , [referencedObjectType]

         , [referecedObjectClass]

         , [isCallerDependent]

         , [isAmbiguous]

         , [referencedObjectID]

    from   cteObjectDependencies cteOD

    order by

           [referencedObject]

         , [referencedObjectType]

end
go

exec sp_MS_marksystemobject '[dbo].[sp_ObjectDependants]'
go

Demo

sp_depends

Sample


use [DBLab]
go

declare @object sysname

set @object = '[school].[usp_TripParticipants_List]'

exec sp_depends @object

Output

sp_depends_20180615_1215PM

[dbo].[sp_ObjectDependants]

Sample


use [DBLab]
go

declare @object sysname

set @object = '[school].[usp_TripParticipants_List]'

exec [dbo].[sp_ObjectDependants]
	@object = @object

Output

objectDependency_20180615_1217PM

Source Code Control

GitHub

DanielAdeniji/TransactSQL.ObjectDependency
Link

Summary

sp_depends tells us the action invoked on dependent objects.

It lets us know whether updates were made or just reads.

Whereas sys.sql_expression_dependencies does not appear to make that distinction.

Transact SQL – STRING_AGG

Background

In our post on MySQL – Information_schema.statistics we spoke glowingly of the GROUP_CONCAT Function.

I really could not find a way around using it in MySQL.

I ran into some difficulties using it and as with problems one just googles for workarounds.

BTW, the MySQL post is here.

SQL Server

Introduction

It seems that in version 2017, MSFT played catch up.

And, added a similar function.

String_Agg

Code


select
        [object]
            = quoteName(tblSS.[name])
              + '.'
              + quoteName(tblSO.[name])

        , [indexID]
            = tblSI.[index_id]

        , [index]
            = tblSI.[name]

        , [indexType]
            = tblSI.[type_desc]

        , [columnList]
            = 
                STRING_AGG 
                (
                      concat
                      (
                          tblSC.[name]
                        , ''
                      )	

                    , ', '
                ) 
                WITHIN GROUP 
                (
                    ORDER BY 
                        tblSIC.[key_ordinal]
                )
            

from   sys.schemas tblSS

inner join sys.objects tblSO

        on   tblSS.[schema_id] = tblSO.[schema_id]
 
inner join sys.indexes tblSI

        on   tblSO.[object_id] = tblSI.[object_id]

inner join sys.index_columns tblSIC

        on   tblSI.[object_id] = tblSIC.[object_id]
        and  tblSI.[index_id]  = tblSIC.[index_id]

inner join sys.columns tblSC

        on   tblSIC.[object_id] = tblSC.[object_id]
        and  tblSIC.column_id = tblSC.column_id

/*
    Skip MS Shipped Objects
*/
where tblSO.is_ms_shipped = 0

/*
    Exclude Included Columns
    Only Include actual Key Columns
*/
and   tblSIC.[key_ordinal] > 0

group by

                quoteName(tblSS.[name])
              + '.'
              + quoteName(tblSO.[name])

        , tblSI.[index_id]

        , tblSI.[name]

        , tblSI.[type_desc]

order by

                quoteName(tblSS.[name])
              + '.'
              + quoteName(tblSO.[name])

        , tblSI.[index_id]

        , tblSI.[name]



Output

 

Crediting

Crediting Dan M for asking the question.

And, Martin Smith for ably.

Simulating group_concat MySQL function in Microsoft SQL Server 2005?
Link

 

References

  1. Microsoft
    • String_Agg
    • sys.index_columns
  2. Stack Overflow
    • Simulating group_concat MySQL function in Microsoft SQL Server 2005?
      Link

SQL Server – Identify Objects with dependencies outside of current database

Background

Needed a quick way to identify referenced objects that are not contained in my current database either to having being dropped, located in a different database, or located on a linked server.

 

DevioBlog

DevioBlog has a good concise query.

A write-up is available here.

And, here it is in it’s entirety.


select o.name, d.referenced_entity_name, *
from sys.sql_expression_dependencies  d

inner join sys.objects o 
       on d.referencing_id = o.object_id

where referenced_id is null


 

Code

 


/*

    sys.sql_expression_dependencies (Transact-SQL)
    v2008
    https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-sql-expression-dependencies-transact-sql?view=sql-server-2017

    ID of the referenced entity. 


    a) The value of this column is never NULL for schema-bound references. 

    b) The value of this column is always NULL for cross-server and cross-database references.

    c) NULL for references within the database if the ID cannot be determined. 

        Objects dropped

    
    d) For non-schema-bound references, the ID cannot be resolved in the following cases:

        The referenced entity does not exist in the database.

*/
select 
          
        [object]
        = quoteName
            (
                isNull
                (
                    tblSS.[name]
                    , ''
                )
            )

            + '.'

            + quoteName
            (
                isNull
                (
                    tblSO.name
                    , ''
                )
            )

        , [objectType]
            = tblSO.[type_desc]

        , [referencedDatabase]
            = tblSED.referenced_database_name

        , [referencedObject]
            = quoteName
                (
                    isNull
                        (
                            tblSED.referenced_schema_name
                            , ''
                        )
                )
                + '.'
                + quoteName
                    (
                        isNull(tblSED.referenced_entity_name, '')
                    )

        , [classofReferencedObject]
            = tblSED.referenced_class_desc

from sys.objects tblSO

inner join sys.schemas tblSS

        on tblSO.schema_id = tblSS.schema_id

inner join sys.sql_expression_dependencies  tblSED

        on tblSO.object_id = tblSED.referencing_id

where (

        (
            ( tblSED.[referenced_id] is null )
        )

    )

Summary

Thank goodness information about referenced objects are cataloged in the sys.sql_expression_dependencies system table.

If the referenced object is not in the contextual database, the referenced_id is logged as null.

Also as part of clean-up effort the referenced_id column is nulled out whenever an object is dropped.

 

SQL Server – OS Bitness – 32 or 64 bit ?

Background

A while ago I googled on how to determine whether I am running on a 32 or 64 bit OS and found contemplative suggestions.

But, bar to implementation was a bit higher than I was willing to pay.

 

SSMS

And, so I was delighted when a few days ago, I rediscovered how Microsoft’s own SQL Server Management Studio ( SSMS) approaches same.

They invoke master..xp_MSVer; an extended stored procedure.

 

Master..xp_MSVer

SQL

Result in Rows


set nocount on
go

set XACT_ABORT on
go

if object_id('tempdb..#SVer') is not null
begin
    drop table #SVer
end

create table #SVer
(
       [Index]				int
    ,  [Name]				sysname
    ,  [Internal_Value]		int
    ,  [Value]				nvarchar(512)
)
insert #SVer 
(
       [Index]
    ,  [Name]
    ,  Internal_Value
    ,  [Value]
)
exec master.dbo.xp_msver
            
select 
        
          tblSV.[Name]
        , tblSV.[Value]

from #SVer tblSV

where [Name] in
(
      'WindowsVersion'
    , 'Platform'
)

if object_id('tempdb..#SVer') is not null
begin
    drop table #SVer
end

Result in Columns



set nocount on
go

set XACT_ABORT on
go

if object_id('tempdb..#SVer') is not null
begin
    drop table #SVer
end

create table #SVer
(
       [Index]				int
    ,  [Name]				sysname
    ,  [Internal_Value]		int
    ,  [Value]				nvarchar(512)
)
insert #SVer 
(
       [Index]
    ,  [Name]
    ,  Internal_Value
    ,  [Value]
)
exec master.dbo.xp_msver

; with cteSV
as
(
    select 
              tblSV.[Name]
            , tblSV.[Value]

    from #SVer tblSV

    where [Name] in
    (
          'WindowsVersion'
        , 'Platform'
    )

)
select *

from (

        select 
                   [WindowsVersion]
                 , [Platform]

        from cteSV

        PIVOT
        (
            max(Value)

            for [Name] in
            (
                   [WindowsVersion]
                 , [Platform]
            )
        ) as tblA

    ) tblA

if object_id('tempdb..#SVer') is not null
begin
    drop table #SVer
end

 

Output

x32

x64