Have you ever wanted to know all of the SQL roles that a given login resides in within any database on a SQL server?  While gathering this information have you ever made the determination that using the graphical interface provided in SSMS to gather this information is cumbersome?

Well… I have.  Because of that I threw this script together.  The below script will return all of the database roles that a given login resides in within any database on the SQL server.  it’s actually a pretty simple little script. It uses a variable called @login which you populate with the login name that you want to search for.  By using a cursor it will move through each database on the server matching the database user with the sid from sys.server_principals and checking the roles to see where the login is hanging out.  It inserts the information into a temp table and then returns the temp table at the end.

System dmv used by this script:

• sys.databases ** – Contains one row per database in the instance of Microsoft SQL Server. http://msdn.microsoft.com/en-us/library/ms178534.aspx
• sys.server_principals ** – Contains a row for every server-level principal.  http://msdn.microsoft.com/en-us/library/ms188786.aspx
• sys.database_principals ** – Returns a row for each principal in a database. http://msdn.microsoft.com/en-us/library/ms187328.aspx
• sys.database_role_members ** – Returns one row for each member of each database role. http://msdn.microsoft.com/en-us/library/ms189780.aspx

use master;
go
set nocount on;
go

if (select object_id('tempdb..#temptable')) is not null
begin
  drop table #temptable;
end;
create table #temptable(
  [dbname] varchar(128) not null
, [member_name] varchar(128) not null
, [role_name] varchar(128) not null
);

declare @dbname varchar(128), @sql varchar(max), @login varchar(128), @sid varbinary(85), @server_principal_id int;
set @login = 'testlogin'

select @sid = [sid] from sys.server_principals where name = @login;

declare cursor1 cursor for
select rtrim(name) from sys.databases where state = 0;
open cursor1;
fetch next from cursor1 into @dbname;
while @@fetch_status = 0
begin
   set @sql = 'select ''' + @dbname + ''' as [dbname], a.name as [member_name], c.name as [role_name]
   from [' + @dbname + '].[sys].[database_principals] as [a], [' + @dbname + '].[sys].[database_role_members] as [b], [' + @dbname + '].[sys].[database_principals] as [c]
   where a.[sid] = ' + sys.fn_varbintohexstr(@sid) + ' and b.member_principal_id = a.principal_id and c.principal_id = b.role_principal_id'
   insert into #temptable exec(@sql);

   fetch next from cursor1 into @dbname;
end
close cursor1;
deallocate cursor1;

select * from #temptable order by dbname;