LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon