March 8, 2005 at 2:41 pm
I have created a role on a database with some fairly extensive permission settings - and I need to reproduce this on a few other servers. Is there any way to script these settings, or will it be a completely manual process? This would be for both convenience and accuracy!
Thanks for the help.....
Jeff
March 8, 2005 at 6:25 pm
Try this to generate a script:
declare @role varchar(15)
set @role = 'abc' -- replace with your role
create table #tempPermissions(owner varchar(50),object varchar(100),grantee varchar(100),grantor varchar(100),ProtectType varchar(15), action varchar(25), [column] varchar(25))
insert into #tempPermissions exec sp_helprotect @username = @role
select 'Grant ' + action + ' on [' + object + '] to ' + @role + char(13) + 'GO' + char(13) from #tempPermissions
where [column] in ('(All+New)','.')
select 'Grant ' + action + ' on [' + object + '] ([' + [column] + ']) to ' + @role + char(13) + 'GO' + char(13) from #tempPermissions
where [column] not in ('(All+New)','.')
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply