Post reply

scripting user-defined roles

  • March 8, 2005 at 2:41 pm

    #86995

    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

    #546039

    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