Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Cleveland, OH
    Posts
    10

    Angry Unanswered: Adding Users to a Role in Bulk

    I have a need to add all the users listed in the sysxlogins table to the db_datawriter role. I wrote a proc that does this. It indicates that each user is successfully added to the role, but they aren't. If I look at the user in EM, they don't have that role checked. I've tried all the obvious stuff like close EM and re-open, etc...it doesn't help.

    Here is the proc
    CREATE proc sp_MyProc
    @DBName varchar(256)
    As

    Declare UID_Cursor Cursor
    For
    Select Name from master..sysxlogins Where Len(Name) = 7 Order By Name

    Open UID_Cursor
    Declare @Name as varchar(256)
    Declare @TempString as varchar(8000)

    Fetch Next from UID_Cursor into @Name


    While (@@Fetch_Status <> -1)
    Begin

    Set @TempString = 'sp_addrolemember ''db_datareader'', ''' + @Name + ''''
    Exec(@TempString)

    Fetch Next from UID_Cursor Into @Name
    End

    Close UID_Cursor
    Deallocate UID_Cursor

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    You may need to use sp_adduser first. How many users are you dealing with? New install, or maintaining a system? Only reason I would see to script to this level would be if this is maintenance. Usually in this sort of situation, I would wrte something like:

    select 'exec sp_adduser ' + name + ', ' + name + ', db_datawriter'
    from sysxlogins

    strip out the rows you don't want from the output, and run the rest.

  3. #3
    Join Date
    Feb 2004
    Location
    Cleveland, OH
    Posts
    10
    Originally posted by MCrowley
    You may need to use sp_adduser first. How many users are you dealing with? New install, or maintaining a system? Only reason I would see to script to this level would be if this is maintenance. Usually in this sort of situation, I would wrte something like:

    select 'exec sp_adduser ' + name + ', ' + name + ', db_datawriter'
    from sysxlogins

    strip out the rows you don't want from the output, and run the rest.


    It's a setup issue. We're in the middle of deploying a new business system. We find the need to create new environments every so often, so we create new databases. Once the database is created, it may or may not copy all of the users into the new database. I have a proc that adds the users to the database, but I need to add all the users to the db_datawriter and db_datareader groups easily.

    My other procs work fine (Adding users, setting default db, granting access to the new db). But adding users to the 2 roles doesn't seem to work in the proc. .

  4. #4
    Join Date
    Feb 2004
    Location
    Cleveland, OH
    Posts
    10

    Sorta Resolved...

    OK. I found the issue, which leads to another question.

    I have a database called "MyObjects" that stores all my objects outside of the application database. I have another database called "Application" that houses the business system.

    My proc for adding users to roles was in the MyObjects database. The users needed to be modified in the Application database. When I ran the proc, it set all the roles in the MyObjects database, and not the Application database.

    This leads to another issue:
    How do I let sp_addrolemember know which database I want it to affect?

  5. #5
    Join Date
    Sep 2003
    Posts
    522
    have you tried to look at the actual stored procedure? with the code below you'd be able to do what you need (haven't tested it, but should be close):

    exec master.dbo.sp_configure 'allow', 1
    reconfigure with override
    go
    declare @roluid smallint, @ruidbyte smallint, @ruidbit smallint
    select @roluid = uid from sysusers
    where name = 'db_datawriter' and issqlrole = 1

    select @ruidbyte = ((@roluid - 16384) / 8) + 1
    , @ruidbit = power(2, @roluid & 7)
    -- update u set roles = convert(varbinary(2048),
    select roles = convert(varbinary(2048),
    substring(convert(binary(2048), roles), 1, (((@roluid - 16384) / 8) + 1)-1)
    + convert(binary(1), (@ruidbit) | substring(convert(binary(2048), roles), @ruidbyte, 1))
    + substring(convert(binary(2048), roles), @ruidbyte+1, 2048-@ruidbyte) ),
    updatedate = getdate()
    from sysusers u inner join master.dbo.sysxlogins x
    on u.sid=x.sid
    go
    exec master.dbo.sp_configure 'allow', 0
    reconfigure with override
    go

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •