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
asked Jul 13 '12 at 10:00 PM in Default
So you want something similar to the following?
answered Jul 13 '12 at 10:53 PM
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
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