Script to copy object permissions

Question

I'm looking to copy permissions from one object to another. I've created a stored proc that has a similar audience as another object, so I would like to just copy those permissions. I was surprised that I couldn't find something like this.

Goal: Exec CopyPermissions ExistingObject, NewObject

I can generate the script needed, but I'm not sure how to execute as part of the proc. Any suggestions?

Here is what I have to generate the script:

Alter Procedure AIT_CopyObjectPermissions @Object as Varchar(70) as declare @permissions table ( [Owner] varchar(50) NULL, [Object] varchar(50) NULL, Grantee varchar(50) NULL, Grantor varchar(50) NULL, [ProtectType] varchar(50) NULL, [Action] varchar(50) NULL, [Column] varchar(10))

insert @permissions Exec sp_helprotect @Object Select 'GRANT '+Action+' ON [dbo].['+Object+'] TO ['+ Grantee +']' from @Permissions

-- Exec CopyObjectPermissions TestProc

Answer 1

So you want something similar to the following?

DECLARE @existingObject NVARCHAR(128) = N'OldObject'
DECLARE @newObject NVARCHAR(128) = N'NewObject'


DECLARE @PermsList TABLE
    (
      TheID INT IDENTITY(1, 1) ,
      DBUser NVARCHAR(128) ,
      state_desc NVARCHAR(128) ,
      permission_name NVARCHAR(128)
    )


INSERT  INTO @PermsList
        ( DBUser ,
          state_desc ,
          permission_name
        )
        SELECT  sdp.name AS DBUser ,
                dp.state_desc ,
                dp.permission_name
        FROM    sys.database_permissions dp
                LEFT OUTER JOIN sys.all_objects so ON dp.major_id = so.object_id
                LEFT OUTER JOIN sys.database_principals sdp ON dp.grantee_principal_id = sdp.principal_id
                LEFT OUTER JOIN sys.schemas s ON so.schema_id = s.schema_id
        WHERE   object_id = OBJECT_ID(@ExistingObject) 

DECLARE @Id INT ,
    @SqlExec NVARCHAR(4000)

SELECT  @Id = MIN(TheId)
FROM    @PermsList
WHERE   TheID IS NOT NULL
WHILE @Id IS NOT NULL 
    BEGIN

        SELECT  @SqlExec = state_desc + ' ' + permission_name + ' on '
                + QUOTENAME(@newObject) + ' to ' + QUOTENAME(DBUser) + ';'
        FROM    @PermsList
        WHERE   TheID = @Id

        PRINT @SqlExec
        EXEC (@SqlExec)


        SELECT  @id = MIN(TheID)
        FROM    @PermsList
        WHERE   TheID > @id
                AND TheID IS NOT NULL
    END

Answer 2

0

I like the idea, but it didn't work. "(0 row(s) affected)" I've only used While loop a couple times. The first part looks similar, just puts the records in a variable from sysobjects instead of coming from a proc.

When I looked at the permissions of the new object in SSMS it also had no permissions assigned.

The good thing about variables is that it doesn't store the data long. The bad thing about variables is that it doesn't store the data long. :) So, it's a little hard to troubleshoot.

answered Jul 13 '12 at 11:15 PM

AUClark
0 ● 1 ● 1

That's why there's the print @SqlExec statement in there, it will dump the permissions to the screen. You could always throw a select * in there against the table variable as well to see what data it gets (or even just run the query with the object name)

Jul 13 '12 at 11:18 PM SirSQL

(comments are locked)

Answer 3

0

My fault - It works perfectly!!!

I just added another param for execution type to be able to see what it's going to do first: IF @ExecType = 'Report' PRINT @SqlExec; IF @ExecType = 'Run' EXEC (@SqlExec);

Thanks for the help! I've had similar circumstances where I could have used this type of functionality as well, so I'm sure I'll get good use out of it.

answered Jul 13 '12 at 11:40 PM

AUClark
0 ● 1 ● 1

(comments are locked)

Script to copy object permissions

Follow this question

Related Questions