Lister les utilisateurs de bases associés à un login
Retrouver, pour un login SQL Server, chaque base où il a un utilisateur, avec son schéma par défaut et ses rôles de base de données.
La première requête renvoie, pour un login SQL Server donné, chaque base de données où ce login possède un utilisateur associé, avec le schéma par défaut et les rôles de base de données de cet utilisateur. La seconde requête donne le même mapping pour toutes les bases de l'instance, dans un seul jeu de résultats.
Requête 1 : un login sur toutes les bases
DECLARE @name SYSNAME = N'your login name'; -- paramètre d'entrée : le nom du login
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;
Requête 2 : tous les utilisateurs et rôles avec 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'
Remarques
- La requête 1 ne lit que les bases dont l'état est ONLINE (
state = 0). - La requête 2 exécute
sp_helpuserdans chaque base et rassemble les résultats dans une table temporaire. Le filtre sur le login de la dernière instruction est un exemple : remplacerAppLoginpar le login recherché.