is this enough to copy user's permissions to another user's from sp_helpprotect

Hi I use exec helprotect to put a user's perimssion into a temp table called #permissions defined with these columns : (DBname,[Owner] ,[Object],[Grantee] , [Grantor] , [ProtectType] , [Action] , [Col] ) like this:

Now when I get those permissions I use this script inside a cursor to replicate each permission to the user: @.newLoginParam

My question: is there any big business scenario in which my script might fail or miss some permissions for any user?

here is the main part of my script:

declare @.objj as varchar(8000)
declare @.grantee as varchar(8000)
declare @.action as varchar(8000)
declare @.col as varchar(8000)
declare @.sqlGrant as varchar(8000)
declare @.ProtectType as varchar(8000)
declare @.count as int
set @.count=0
declare crsMyTblPermis cursor for
select Object,Grantee,ProtectType , Action, Col from #permissions
open crsMyTblPermis
fetch next from crsMyTblPermis into @.objj, @.grantee, @.ProtectType, @.action,@.col
while @.@.fetch_status=0
begin

if (@.objj!='.' and (@.col = '.' OR @.col = '(All+New)'))
begin
set @.sqlGrant = @.ProtectType +@.action + ' on ' + @.objj + ' to ' + @.newLoginParam
end

if (@.objj!='.' and @.col != '.' and @.col != '(All+New)')
begin
set @.sqlGrant = @.ProtectType +@.action + ' on ' + @.objj + '(' + @.col + ') to ' + @.newLoginParam
end

if (@.objj='.')
begin
set @.sqlGrant = @.ProtectType +@.action + ' to ' + @.newLoginParam
end

exec(@.sqlGrant)
fetch next from........

...etc

P.S: The main reason is that I can t test my script now on the production platform on which the script will be run

Thanks for your advice .

I think you nead a space between @.ProtectType and action.

If you need to grant the permission of an existing user to many new users consider using a database role. You can grant the permissions to the role and then just add the new users to the role.

|||

yeah i will also add the new user to the roles that the old user was part of as well. But i guess the old user might also have permissions on databse objects without necessarily belonging to a specific role?do you agree?

Thanks