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

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 )

w

Connecting to %s