SQL Server – Script for Granting Permissions

Background

As databases are restored from Production to lower level environments, our users complain that they have spent so much time provisioning permissions in the destination environment.

And, so as part of the database request they place in a little requirement, “please preserve current permissions and re-apply upon database restore“.

 

Processing

Since we are in SQL we will capture those permissions in tables and write a script that will read through the table and apply the corresponding permission set.

Database Objects

Overview

  1. Table variable
    • dbo.udttPrincipal
    • dbo.udttSecurable
  2. Stored Procedure
    • dbo.sp_grantPrincipalPermissionToSecurable

SQL

dbo.udttPrincipal


use master
go

/*

    drop type [udttPrincipal]

*/
declare @type sysname

set @type = 'udttPrincipal'

if not exists
    (
        select *

        from   sys.types tblST

        where  tblST.[name] = @type

    )
begin

    create type [dbo].[udttPrincipal] as table
    (
          [id]      smallint not null
            identity(1,1)

        , [name]	sysname

        , primary key
            (
                [name]
            )
            WITH 
            (
                IGNORE_DUP_KEY = ON
            )
    
        , unique 
            (
                [id]
            )
            WITH 
            (
                IGNORE_DUP_KEY = ON
            )

    ) 

end
go

 

dbo.udttSecurable


use master
go

/*

    drop type [udttSecurable]

*/
declare @type sysname

set @type = 'udttSecurable'

if not exists
    (
        select *

        from   sys.types tblST

        where  tblST.[name] = @type

    )
begin

    create type [dbo].[udttSecurable] as table
    (

          [id]      smallint not null
            identity(1,1)

        , [database]	sysname not null

        , [securable]		sysname not null

        , [securableType] sysname null
            default 'object'
             
        , [permission]   varchar(200) not null
        
        , primary key
            (
                  [database]
                , [securable]
            )
            WITH 
            (
                IGNORE_DUP_KEY = ON
            )
            
            
        , unique 
            (
                [id]
            )
            WITH 
            (
                IGNORE_DUP_KEY = ON
            )
            
    ) 

end
go



dbo.sp_grantPrincipalPermissionToSecurable


use [master]
go

/*
    drop proc [dbo].[sp_grantPrincipalPermissionToSecurable]
*/

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

    exec('create procedure [dbo].[sp_grantPrincipalPermissionToSecurable] as print ''shell'' ')

end
go


alter procedure [dbo].[sp_grantPrincipalPermissionToSecurable] 
(
      @tblPrincipal [dbo].[udttPrincipal] readonly
    , @tblSecurable	[dbo].[udttSecurable] readonly
    , @debug		bit = 0
    , @scriptOnly   bit = 0
    , @showGeneratedSQL bit = 0
)
as
begin

    set nocount on;
    set XACT_ABORT on;

    declare @FORMAT_SQL_ADDUSER_POSITIONAL varchar(600);
     
    declare @FORMAT_SQL_GRANTOBJECTACCESS_POSITIONAL varchar(600)

    declare @sql				nvarchar(600);

    declare @idPrincipal        smallint
    declare @idMaxPrincipal     smallint


    declare @idDatabase			smallint
    declare @idMaxDatabase		smallint


    declare @dbname				sysname
    declare @securable			sysname
    declare @securableType		sysname
    declare @principal			sysname
    declare @permission         varchar(200)

    declare @idSecurable        smallint
    declare @idSecurableMax     smallint


    declare @databaseContext	varchar(30)

    declare @tblDatabase TABLE
    (
          [id] int not null
                identity(1,1)

        , [name]  sysname not null

    )

    declare @tblExistenceCheck TABLE
    (

          [id] int not null
            identity(1,1)

        , [securableType] sysname not null

        , [container]	  sysname not null
            
        , [sql]           varchar(200) not null

    )

    declare @tblScript TABLE
    (
          [id] int not null
                identity(1,1)

        , [database]      sysname not null
        , [principal]     sysname not null
        , [securable]     sysname null
        , [securableType] sysname null

        , [sqlStatement]  nvarchar(600) not null

    )

    declare @CHAR_TAB			char(1)
    declare @CHAR_NEWLINE		char(2)

    declare @iProcessingMode smallint

    declare @MODE_FORMATMESSAGE smallint
    declare @MODE_XP_SPRINTF smallint

    declare @existenceCheckSQL varchar(300);
    declare @strLog				nvarchar(4000);

    set @MODE_FORMATMESSAGE = 0
    set @MODE_XP_SPRINTF = -1

    set @iProcessingMode = @MODE_FORMATMESSAGE

    set @CHAR_TAB = char(9);
    set @CHAR_NEWLINE = char(13) + char(10);

    set  @FORMAT_SQL_ADDUSER_POSITIONAL
             = 'use [%s]; '
                + ' if user_id(''%s'') is null '
                + ' begin  '
                + ' print ''add user %s''; '
                + '   create user [%s] from LOGIN [%s] '
                + ' end ';



    set  @FORMAT_SQL_GRANTOBJECTACCESS_POSITIONAL
             = 'use [%s]; ' -- database
                + ' %s ' -- conditional clause
                + ' begin '
                + ' print ''Processing object %s %s'' ; ' -- database and securable
                + ' grant %s on %s to [%s];  ' -- permission set, securable, principal
                + ' end ';

    insert into @tblExistenceCheck 
    (
          [securableType]
        , [container]
        , [sql]
    )
    select 'object', 'sys.objects', 'if object_id(''%s'') is null'
    union
    select 'schema', 'sys.schemas', 'if schema_id(''%s'') is null'
    union
    select 
              'certificate'
            , 'sys.certificates'
            , 'if not exists ( select * from sys.certificates where name = ''%s'') ' 
    union
    select 
              'symmetric keys'
            , 'sys.symmetric_keys'
            , 'if not exists ( select * from [sys].[symmetric_keys] where name = ''%s'') ' 

    /*
        Get a list of databases
    */
    insert into @tblDatabase
    (
        [name]
    )
    select distinct
            tblO.[database]
    
    from   @tblSecurable tblO

    set @idPrincipal = 1
    set @idMaxPrincipal = ( select max([id]) from @tblPrincipal )

    set @idDatabase = 1
    set @idMaxDatabase = ( select max([id]) from @tblDatabase )

    /*
        Create Principals if they do not exist
    */
    while (@idDatabase <= @idMaxDatabase)
    begin

        set @databaseContext = null;

        /*
            Get database
        */
        select @databaseContext = tblD.[name]
        from   @tblDatabase tblD
        where  tblD.id = @idDatabase

        if (@databaseContext is not null)
        begin

            set @idPrincipal = 1

            /*
                Iterate principal list
            */
            while (@idPrincipal <= @idMaxPrincipal)
            begin

                set @principal = null;
                set @sql = null

                /*
                    Get Contextual Principal
                */
                select @principal = tblP.[name]
                from   @tblPrincipal tblP
                where  tblP.id = @idPrincipal

                /*
                    Prepare SQL for Creating Principal
                */
                if (@iProcessingMode = @MODE_FORMATMESSAGE)
                begin
                            
                    set @sql = formatmessage
                    (
                               @FORMAT_SQL_ADDUSER_POSITIONAL
                             , @databaseContext
                             , @principal -- if user_id
                             , @principal -- print add user
                             , @principal -- database user
                             , @principal -- user login
                    )

                end
            
                else if (@iProcessingMode = @MODE_XP_SPRINTF)
            
                begin
                            
                    exec master.dbo.xp_sprintf
                               @sql output
                             , @FORMAT_SQL_ADDUSER_POSITIONAL
                             , @databaseContext
                             , @principal -- if user_id
                             , @principal -- print add user
                             , @principal -- database user
                             , @principal -- user login

                end

                /*
                    Print SQL for Creating Principal :
                        if in debug mode
                              or in scriptOnly mode
                */
                if (
                           (@debug =1)
                        or (@scriptOnly = 1)
                    )
                begin

                    print @sql
                
                end
                
                /*
                    Track Generated SQL
                */
                insert into @tblScript
                (
                      [database]
                    , [principal] 
                    , [sqlStatement]
                )
                values
                (
                      @databaseContext
                    , @principal
                    , @sql
                )

                /*
                    If not in @script mode then apply sql to DB
                */
                if (@scriptOnly = 0)
                begin

					begin try

						exec(@sql)

					end try

					begin catch

						/*
							display error
						*/
						set @strLog = ''
								+ @CHAR_TAB
								+ '@@ERROR :- ' + cast(@@ERROR as varchar(30))
								+ @CHAR_NEWLINE
								+ @CHAR_TAB
								+ 'ERROR_MESSAGE :- ' + ERROR_MESSAGE() 
								+ @CHAR_NEWLINE

						print @strLog

					end catch

				end -- if (@scriptOnly = 0)

                -- move to next principal
                set @idPrincipal = @idPrincipal + 1

            end

        end

        -- move to next database
        set @idDatabase = @idDatabase + 1


    end


    set @idSecurable = 1
    set @idSecurableMax = ( select max([id]) from @tblSecurable)

    while ( @idSecurable <= @idSecurableMax)
    begin

        /* 
            Reset local variables
        */
        select
              @databaseContext = null
            , @securable = null
            , @permission = null
            , @securable  = null
            , @securableType = null


        /* 
            Get contextual data
        */
        select
              @databaseContext = tblO.[database]
            , @permission = tblO.[permission]
            , @securable = tblO.[securable]
            , @securableType = tblO.[securableType]
            , @existenceCheckSQL = tblEC.[sql]

        from @tblSecurable tblO

        left outer join @tblExistenceCheck tblEC

                on tblO.[securableType] = tblEC.securableType

        where  tblO.[id] = @idSecurable

        if (@securable is not null)
        begin
            
            set @sql = null

            /* 
                Prepare SQL
            */
            if (@iProcessingMode = @MODE_FORMATMESSAGE)
            begin
                            
                set @sql = formatmessage
                (
                          @FORMAT_SQL_GRANTOBJECTACCESS_POSITIONAL
                        , @databaseContext
                        , @existenceCheckSQL
                        , @databaseContext
                        , @securable 
                        , @permission
                        , @securable 
                        , @principal
                )

            end
            
            else if (@iProcessingMode = @MODE_XP_SPRINTF)
            begin

                exec master.dbo.xp_sprintf
                            @sql output
                        , @FORMAT_SQL_GRANTOBJECTACCESS_POSITIONAL
                        , @databaseContext
                        , @existenceCheckSQL
                        , @securable
                        , @permission
                        , @securable 
                        , @principal

            end

            if (
                       ( @debug =1)
                    or ( @scriptOnly = 1)
                )
            begin

                print @sql

            end

            /*
                Track Generated SQL
            */
            insert into @tblScript
            (
                  [database]
                , [principal]
                , [securable]     
                , [securableType]
                , [sqlStatement]
            )
            values
            (
                  @databaseContext
                , @principal
                , @securable 
                , @securableType 
                , @sql
            )

            /*
                Execute Generated SQL
                    when not in scriptOnly mode
            */
            if (@scriptOnly = 0)
            begin

                begin try

                    exec(@sql)

                end try

                begin catch

                    /*
                        display error
                    */
                    set @strLog = ''
                            + @CHAR_TAB
                            + '@@ERROR :- ' + cast(@@ERROR as varchar(30))
                            + @CHAR_NEWLINE
                            + @CHAR_TAB
                            + 'ERROR_MESSAGE :- ' + ERROR_MESSAGE() 
                            + @CHAR_NEWLINE

                    print @strLog

                end catch

            end -- if (@scriptOnly = 0)


        end -- if @securable is not null

        -- move to next object

        set @idSecurable = @idSecurable + 1

    end -- while ( @idSecurable <= @idSecurableMax)

    /*
        If we are showing result set, then let us do so
    */
    if (@showGeneratedSQL = 1)
    begin

        select *
        from   @tblScript

    end
end
go

Sample Invocation

Query Batch 1


use master
go

set nocount on
go

set XACT_ABORT on
go

declare @tblPrincipal [dbo].[udttPrincipal]
declare @tblSecurable [dbo].[udttSecurable]

declare @permission varchar(200)
declare @scriptOnly  bit
declare @showGeneratedSQL  bit


set @scriptOnly = 0
set @showGeneratedSQL =1
set @permission = 'select, insert, update, delete ';


insert into @tblPrincipal
(
    [name]
)
select 'LAB\svcWeb'
union
select 'LAB\svcBatch'


insert into @tblSecurable
(
      [database]
    , [securable]
    , [permission]
)
select 'dem', '[dbo].[root]', @permission
union
select 'dem', '[dbo].[telecom]', @permission

exec [dbo].[sp_grantPrincipalPermissionToSecurable]
          @tblPrincipal = @tblPrincipal
        , @tblSecurable= @tblSecurable
        , @showGeneratedSQL = @showGeneratedSQL
        , @scriptOnly = @scriptOnly


Source Code Control

GitHub

Placed in Github

DanielAdeniji/SQLGrantPermissionToSecurable
Link

 

Summary

In this post we are forced to confront a few hidden truths:

  1. formatmessage
    • xp_sprintf
      • Much better and reliable than xp_sprintf
  2. sp_executeSQL
    • Support
      • Only supports DML
      • Does not support DDL

SSIS – “Could not load package because of error 0xC00160AE ” – “Access is Denied”

Error

Error Message

Could not load package “\MSDB\ImportData” because of error 0xC00160AE.
Description: Connecting to the Integration Services service on the computer “LAB-DB” failed with the following error: “Access is denied.”

Error Image

 

Remediation

Component Services

Global

Objective

Add SQL Server Agent Proxy Account to the local “Distributed COM Users” group.

This allows the proxy account to launch and activate all COM Objects on the local machine.

Image

Specific Objects

Steps

  1. Launch “Component Services” and navigate to each “Microsoft SQL Server Integration Services” component.
  2. Right click on each such component and grant permission to each Integration Service Component.
    • Access the Services Tab
      • Group box :- “Launch and Activation” permissions
        • Access the “Launch and Activation” permissions group box
        • Ensure the “Customize” option is chosen
        • Click the “Edit” button
        • Grant your proxy account “Local Launch” and “Local Activation” access
      • Group box :- Access permissions
        • Access the “Access” permissions group box
        • Ensure the “Customize” option is chosen
        • Click the “Edit” button
        • Grant your proxy account “Local Access
    • Restart relevant Sql Server Integration Services

Images

Microsoft SQL Server Integration Services
Security
Security – Launch and Activation Permission

 

Security – Access Permission

 

Services Applet

SQL Server

msdb

Objective

Please make sure that proxy account has access to the msdb database and that it has been granted membership in the db_ssisoperation role.

Syntax


USE [msdb]
GO
if user_id('account') is null
begin

    CREATE USER [proxyAccount] FOR LOGIN [proxyAccount]

end

ALTER ROLE [db_ssisoperator] 
   ADD MEMBER [proxyAccount]
GO

References

  1. Microsoft
    • Docs
      • Docs / SQL / SQL Server / Integration Services / Service
        • Integration Services Service (SSIS Service)
          Link
    • Technet
      • Integration Services > Integration Services Features and Tasks > Security
        • Integration Services Roles (SSIS Service)
          Link
  2. StackExchange
    • How do I grant the privilege of connecting to SSIS?
      Link

SQL Server – Database Permissions – List

Background

There are different levels of permissions in SQL Server.

There are concentric permissions.

Inclusive are Instance, database, schema, and object.

And, others that are more dependant on the object type.

Database Permissions

Code


SELECT
            [database]
              = db_name()

         ,  [username] 
              = tblGranteeDP.[name]

         ,  [login]     
             = suser_sname(tblGranteeDP.[sid])

         ,  [permissionClass]
             = tblDP.class_desc

         ,  [permissionName] 
            = tblDP.[permission_name]

         ,  [permissionState] 
            = tblDP.state_desc

         ,  [principalType] 
            = tblGranteeDP.[type_desc]

FROM sys.database_permissions AS tblDP

INNER JOIN sys.database_principals AS tblGrantorDP   
    ON tblGrantorDP.principal_id = tblDP.grantor_principal_id

INNER JOIN sys.database_principals AS tblGranteeDP
    ON tblGranteeDP.principal_id = tblDP.grantee_principal_id

LEFT OUTER JOIN sys.server_principals AS tblGranteeSP
    ON tblGranteeDP.principal_id = tblGranteeSP.principal_id

/*
    Identifies class on which permission exists.
*/
where (tblDP.class = 0)

/*
    Exclude those with mere CONNECT permission
*/
and  tblDP.permission_name not in 
        (   
            'CONNECT'
        )

order by
      db_name() asc
    , tblGranteeDP.[name] asc
    , tblDP.[permission_name] asc

Output

SQL Server – Security – Privileging & Tracking the Guest Account

Background

Each month we get databases from our Vendors.  And, we have to review data in those tables.

There are a couple of pathways to quickly granting accessing to our users.

In this post, we will grant access to the guest user.

 

SQL

Grant Access



grant connect to guest;

Review Access

Review Permissions grant/denied to Guest on Contextual Database

Code


declare @principal sysname

set @principal = 'guest'

; with cteObject
(
      objectID
    , [object]
    , schemaID
    , [schema]
)
as
(
    select
              tblSO.object_id
            , tblSO.[name]
            , tblSO.schema_id
            , tblSS.[name]

    from   sys.objects tblSO

    inner join sys.schemas tblSS

            on tblSO.[schema_id] = tblSS.[schema_id]
)
select 

          tblSP.[name]
        
        --, tblSDP.*

        , tblSDP.[state_desc]

        , tblSDP.[permission_name]

        , [objectClass]
            = tblSDP.class_desc

        , [object]
            = coalesce
                (
                     quoteName
                        (
                            cteO.[schema]
                        )
                    + '.'
                    + quoteName
                        (
                            cteO.[object]
                        )

                    , null
                )

            , [grantee]
                = SUSER_NAME(tblSDP.grantor_principal_id)

from   sys.database_principals tblSP

inner join sys.database_permissions tblSDP

    on tblSP.principal_id = tblSDP.[grantee_principal_id]

left outer join cteObject cteO

    on tblSDP.major_id = cteO.objectID

where tblSP.[name] = @principal

order by

    tblSP.[name] asc


Review Access for contextual database

Code

use master
go

set nocount on
go

set XACT_ABORT on
go

declare @tblResult table
(
      [database]    sysname
    , [principal]   sysname
    , [access]      sysname
    , [grantee]     sysname null
)

declare @command nvarchar(4000)

set @command =
                'if databasepropertyex(''?'', ''Collation'') is not null '
                + ' begin '
                + ' use [?]; '
                + ' select [database] = db_name(), tblSP.name,  tblSDP.[state_desc], SUSER_NAME(tblSDP.grantor_principal_id) '
                + ' from sys.database_principals tblSP '
                + ' inner join sys.database_permissions tblSDP '
                + ' on tblSP.principal_id = tblSDP.grantee_principal_id '
                + ' where tblSP.[name] = ''guest'' '
                + ' and tblSDP.[permission_name] = ''CONNECT'' '
                + ' and tblSDP.[state_desc]  = ''GRANT'' '
                + ' end '

insert @tblResult
(
      [database]    
    , [principal]   
    , [access]      
    , [grantee] 
)
exec master.dbo.[sp_MSforeachdb]
        @command1 = @command

select *

from   @tblResult tblR

order by 
    tblR.[database]

SQL Server – Scripting Drop User/Schema for empty schemas

Background

Early this morning I initiated a process to move a database from Production to QA.

As I tried to drop the users from the originating environment, I ran into the error pasted below:

drop user [LAB\ITV-IIS01$]
Msg 15138, Level 16, State 1, Line 13
The database principal owns a schema in the database, and cannot be dropped.

 

Origin

I know that the schema mentioned is a shell.  It was simply created as part of our user creation script.

Using SQL Server Management Studio, we can script the user creation script.

And, here is a sample.


CREATE USER [LAB\ITV-IIS01$]
FOR LOGIN [LAB\ITV-IIS01$] 
WITH DEFAULT_SCHEMA=[LAB\ITV-IIS01$]
GO

Remediation

Let us programmatically identify and script out those users.

SQL

Stored Procedure

dbo.sp_ScriptDropUsersWithSchemas


use master
go

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

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

end
go

-- exec sp_helpuser
alter procedure [dbo].[sp_ScriptDropUsersWithSchemas] 
as
begin

	;  with cteSystemUser
	(
		[username]
	)
	as
	(
		select 'dbo'
		union
		select 'guest'
		union
		select 'INFORMATION_SCHEMA'
		union
		select 'sys'
	)
	, cteSystemSchema
	(
		[schema]
	)
	as
	(
		select 'INFORMATION_SCHEMA'
		union
		select 'sys'
	)
	, cteObjectSchema
	(
		  [schema]
		, [object]
		, [schemaOwnerID]
		, [schemaOwnername]
	)
	as
	(
		select 
				  [schema] = tblSS.[name]
				, [object] = tblSO.[name]
				, [schemaOwnerID] = tblSS.principal_id
				, [schemaOwnername] = user_name(tblSS.principal_id)

		from   sys.schemas tblSS

		inner join sys.objects tblSO

				on tblSS.[schema_id] = tblSO.[schema_id]

		/*
			Skip System Schemas
		*/
		where  tblSS.[name] not in
					(
						select [schema]
						from   cteSystemSchema
					)
	)
	, cteUser
	(
		  [username]
		, [usertype]
		, [userSchemaDefault]
		, [userCreateDate]
		, [schema]
		, [sqlCommandDropSchema]
		, [sqlCommandDropUser]
	)
	as
	(
		select 
				  [username] = tblSDP.[name]
				, [usertype] = tblSDP.[type_desc]
				, [userSchemaDefault] = tblSDP.default_schema_name
				, [userCreateDate]= tblSDP.[create_date]

				, [schema] = tblSS.[name]

				, [sqlCommandDropSchema]
					= 'if schema_id(''' + tblSS.[name] + ''')' 
							+ ' is not null '
							+ ' begin '
							+ ' print ''drop schema '
							+ quoteName(tblSS.[name])
							+ ''' '
							+ '; '
							+ ' DROP SCHEMA '
							+ quoteName(tblSS.[name])
							+ ' end '

				, [sqlCommandDropUser]
					= 'if user_id(''' + tblSDP.[name] + ''')' 
							+ ' is not null '
							+ ' begin '
							+ ' print ''drop user '
							+ quoteName(tblSDP.[name])
							+ ''' '
							+ '; '
							+ ' DROP USER '
							+ quoteName(tblSDP.[name])
							+ ' end '

		from   sys.database_principals tblSDP

		left outer join sys.schemas tblSS

				on tblSDP.[name] = tblSS.[name]

		where tblSDP.[type_desc] in
				(
					  'SQL_USER'
					, 'WINDOWS_USER'
				)

		and  tblSDP.default_schema_name not in
				(
					'dbo'
				)
	)

	select 
			cteU.*

	from   cteUser cteU

	left outer join cteSystemUser cteSU

		on cteU.username = cteSU.username

	/*
		Skip System Users
	*/
	where cteSU.[username] is null

	/*
		Ensure that user does not own objects
	*/
	and not exists
		(	
	
			select 1

			from   cteObjectSchema cteOS

			where  cteU.[username] = cteOS.[schemaOwnername]

		)

end
go

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

Output