Post reply

Copy permissions from role x to role y

  • December 10, 2001 at 12:16 am

    #157629

    I have a database with >500 sp and >100 table. How can I transfer all the permissions from one role to a different role with TSQL?

    I don't want to use DMO because clients without the sql client installed will not be able to run this.

    Thomas

  • December 10, 2001 at 4:38 am

    #420911

    I dont think there is any reason you cant use DMO in your app - should qualify for redistribution. Still, you can copy a role pretty easily in TSQL. Basically you need to do these 3 steps:

    1) Create the new role using sp_addrole.

    2) Add the user (assuming it already exists at this point) to the role using sp_addrolemember.

    3) Copy the permissions by selecting the rows that match from sysprotects (where UID=UID of original role) and inserting them into the same table with the new UID of the new user.

    Andy

    Andy
    SQLAndy - My Blog!
    Connect with me on LinkedIn
    Follow me on Twitter

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply