x

Script to copy object permissions

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

more ▼

asked Jul 13, 2012 at 10:00 PM in Default

AUClark gravatar image

AUClark
0 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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
more ▼

answered Jul 13, 2012 at 10:53 PM

SirSQL gravatar image

SirSQL
4.8k 1 3

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Jul 13, 2012 at 11:15 PM

AUClark gravatar image

AUClark
0 1 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, 2012 at 11:18 PM SirSQL
(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Jul 13, 2012 at 11:40 PM

AUClark gravatar image

AUClark
0 1 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x726
x87

asked: Jul 13, 2012 at 10:00 PM

Seen: 1057 times

Last Updated: Jul 13, 2012 at 11:40 PM