Post reply

Easy way to add users to a role?

  • February 28, 2006 at 10:26 am

    #112967

    Is there an easy way to make every user that is a member of Role A to also be a member of Role B?

    Thanks in advance.

    Justin

  • February 28, 2006 at 3:57 pm

    #623818

    Easy -yes. But it is 2 step process.

    The following will generate a list of sp_addrolemember statements for each user of the role db_datareader to add them to db_datawriter role. Four single quotes in a row will amount to one single quote in the dynamic statement. Then you will just run the generated statements after copying them to the query window. One step process will be a more automated solution but you have to use a cursor. I would not advise to use a third approach and to add rows to sysmembers directly.

    select u.name, m.memberuid into ##temptable

    from sysmembers m join sysusers u

    on m.memberuid =u.uid

    where groupuid = (

    select uid from sysusers where name ='db_datareader')

    select 'Exec sp_addrolemember '+ ''''

    + 'db_datawriter'+'''' +',  '

    + '''' + name+'''' from ##temptable

    drop table ##temptable

    Regards,Yelena Varsha

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

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