sp_addrolemember

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

You could script it out with something like thisECLARE
@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

And of course the role needs to exist already in each individual database.

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

That script is buggy, unfortunately. Try something likeECLARE
@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

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.

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.

I'm sorry i didn't manage to query it. Thank you anyway.

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