List the database users mapped to a login
Find every database where a SQL Server login has a user, with its default schema and database roles.
The first query returns, for one SQL Server login, each database where the login has a mapped user, with the user's default schema and database roles. The second query lists the same mapping for every database on the instance in one result set.
Query 1: one login across all databases
DECLARE @name SYSNAME = N'your login name'; -- input parameter: the login name
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'UNION ALL SELECT N''' + REPLACE(name,'''','''''') + ''',
p.name, p.default_schema_name, STUFF((SELECT N'','' + r.name
FROM ' + QUOTENAME(name) + N'.sys.database_principals AS r
INNER JOIN ' + QUOTENAME(name) + N'.sys.database_role_members AS rm
ON r.principal_id = rm.role_principal_id
WHERE rm.member_principal_id = p.principal_id
FOR XML PATH, TYPE).value(N''.[1]'',''nvarchar(max)''),1,1,N'''')
FROM sys.server_principals AS sp
LEFT OUTER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS p
ON sp.sid = p.sid
WHERE sp.name = @name '
FROM sys.databases WHERE [state] = 0;
SET @sql = STUFF(@sql, 1, 9, N'');
PRINT @sql;
EXEC master.sys.sp_executesql @sql, N'@name SYSNAME', @name;
Query 2: all users and roles with sp_helpuser
SET NOCOUNT ON
CREATE TABLE #temp
(
SERVER_name SYSNAME NULL ,
Database_name SYSNAME NULL ,
UserName SYSNAME ,
GroupName SYSNAME ,
LoginName SYSNAME NULL ,
DefDBName SYSNAME NULL ,
DefSchemaName SYSNAME NULL ,
UserID INT ,
[SID] VARBINARY(85)
)
DECLARE @command VARCHAR(MAX)
--this will contain all the databases (and their sizes!)
--on a server
DECLARE @databases TABLE
(
Database_name VARCHAR(128) ,
Database_size INT ,
remarks VARCHAR(255)
)
INSERT INTO @databases--stock the table with the list of databases
EXEC sp_databases
SELECT @command = COALESCE(@command, '') + '
USE ' + database_name + '
insert into #temp (UserName,GroupName, LoginName,
DefDBName, DefSchemaName,UserID,[SID])
Execute sp_helpuser
UPDATE #TEMP SET database_name=DB_NAME(),
server_name=@@ServerName
where database_name is null
'
FROM @databases
EXECUTE ( @command )
SELECT loginname ,
UserName ,
Database_name
FROM #temp
WHERE LoginName = 'AppLogin'
Notes
- Query 1 reads only databases whose state is ONLINE (
state = 0). - Query 2 runs
sp_helpuserin every database and collects the results in a temporary table. The login filter in the last statement is a placeholder: replaceAppLoginwith the login you need.