SQL Server Performance

sp_addrolemember

Discussion in 'SQL Server 2008 General DBA Questions' started by klehonia, Nov 9, 2009.

  1. klehonia New Member

    Hi, how can i add a database role to one user for all databases?
    Thank you.
  2. melvinlusk Member

    You could script it out with something like this:DECLARE
    @pstrDBName as varchar(50)DECLARE
    @pstrSQL as varchar(50)create
    table #tmpDBName(
    fstrDBName varchar(50)insert
    into #tmpDBNameselect
    name from sysdatabases where name not in ('master','tempdb','model','msdb')while
    exists (select 1 from #tmpDBName)BEGIN
    set @pstrSQL = (select 'use ' + @pstrDBName + CHAR(13) + CHAR(10) + 'GO ' + CHAR(13) + CHAR(10)
    +
    'EXEC sp_addrolemember N''db_YOURROLE'', ''YOURDOMAINADGroup''' +
    CHAR(13) + CHAR(10) + 'GO')exec
    (@pstrSQL)delete
    from #tmpDBName where fstrDBName = @pstrDBNameEND
  3. Adriaan New Member

    And of course the role needs to exist already in each individual database.
  4. klehonia New Member

    Thank you for your reply. I run this script but never stops. It writes messages like (0 row(s) affected) endlessly.
  5. Adriaan New Member

    That script is buggy, unfortunately. Try something like:DECLARE
    @strWorking as varchar(200)DECLARE c CURSOR
    FOR
    SELECT name FROM sysdatabases WHERE name NOT IN ('master','tempdb','model','msdb')
    OPEN c
    FETCH NEXT FROM c INTO @strWorking
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @strWorking = 'EXEC [' + @strWorking + ']..sp_addrolemember N''db_YOURROLE'', ''YOURDOMAINADGroup'''
    EXEC (@strWorking)
    FETCH NEXT FROM c INTO @strWorking
    END
    CLOSE
    c
    DEALLOCATE c

  6. klehonia New Member

    With that script i can add a role to a user that the database already has. I want to add an existed sql login user to a database and a role to that user too.
    Thank you.
  7. Adriaan New Member

    You have the framework to loop through the databases, and to build a command as dynamic SQL.
    Just add a statement to the dynamic SQL to grant db access to the user. Note that you can do more than one command in a single statement.
    By the way, you do not add a role to a user, but you add a user to a role.
  8. klehonia New Member

    I'm sorry i didn't manage to query it. Thank you anyway.
  9. klehonia New Member

    I made it!!!!!!! That's work fine.
    DECLARE @strWorking as varchar(200)
    DECLARE @strdb as varchar(200)

    DECLARE c CURSOR
    FOR
    SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','tempdb','model','msdb')

    OPEN c
    declare b cursor
    FOR
    SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','tempdb','model','msdb')
    open b
    FETCH NEXT FROM c INTO @strWorking
    FETCH NEXT FROM b INTO @strdb

    WHILE @@FETCH_STATUS = 0
    BEGIN
    set @strdb = 'USE [' + @strdb + '] CREATE USER [tt] FOR LOGIN [tt]'
    exec (@strdb)
    SET @strWorking = 'EXEC [' + @strWorking + ']..sp_addrolemember N''db_datareader'', ''tt'''
    EXEC (@strWorking)
    FETCH NEXT FROM b INTO @strdb
    FETCH NEXT FROM c INTO @strWorking

    END

    CLOSE c
    close b
    DEALLOCATE c
    deallocate b


Share This Page