主页 / dba / 问题 / 81595
Accepted
Michael J Swart
Asked: 2014-11-01 07:06:51 +0800 CST

列出给定登录名的所有映射用户的查询

  • 772

查看特定登录名的属性时，可以看到映射到该登录名的用户列表： 在此处输入图像描述

我分析了 SQL Server Management Studio (SSMS)，发现 SSMS 一次连接到每个数据库，并从 sys.database_permissions 检索信息

是否可以编写单个查询来检索上面显示的用户映射信息，或者我是否被迫使用游标或 sp_MSforeachdb 或类似的东西？

sql-server sql-server-2008

8 个回答

  1. Best Answer

    这是使用动态 SQL 的一种方法。如果不进行迭代，实际上没有任何方法可以做到这一点，但这种方法比无证、不受支持和有缺陷的选项（例如sp_MSforeachdb（此处和此处的背景））安全得多。

    这将获得所有在线数据库的列表、映射用户（如果存在）、默认模式名称以及它们所属角色的逗号分隔列表。

    DECLARE @name sysname = N'your login name'; -- input param, presumably

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'UNION ALL SELECT N''' + REPLACE(name,'''','''''') + ''',
    p.name                 COLLATE SQL_Latin1_General_CP1_CI_AS, 
    p.default_schema_name  COLLATE SQL_Latin1_General_CP1_CI_AS, 
    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;

    在更现代的版本（2017+）上，我仍然会使用动态 SQL，但我会使用STRING_AGG()而不是FOR XML PATH，可能是这样的：

    DECLARE @login sysname = N'your login name';

DECLARE @sql  nvarchar(max), 
        @sid  varbinary(85),
        @coll nvarchar(64) = N'COLLATE SQL_Latin1_General_CP1_CI_AS';

SELECT @sid = [sid] FROM sys.server_principals AS dp WHERE name = @login;

;WITH d AS 
(
  SELECT dbid = CONVERT(varchar(11), database_id),
         qn = QUOTENAME(name)
    FROM sys.databases WHERE [state] = 0
)
SELECT @sql = STRING_AGG(CONVERT(nvarchar(max),
        N'SELECT db = d.name, username = dp.name ' + @coll + ', 
        schemaname = dp.default_schema_name ' + @coll + ',
        roles = STRING_AGG(rp.name ' + @coll + ', N'','')
        FROM sys.databases AS d
        LEFT OUTER JOIN ' + qn + '.sys.database_principals AS dp ON dp.sid = @sid
        LEFT OUTER JOIN ' + qn + '.sys.database_role_members AS rm
        ON dp.principal_id = rm.member_principal_id
        LEFT OUTER JOIN ' + qn + '.sys.database_principals AS rp
        ON rp.principal_id = rm.role_principal_id
        WHERE d.database_id = ' + dbid + N'
        GROUP BY d.name, dp.name, dp.default_schema_name'
    ), char(13) + char(10) + N' UNION ALL ')
FROM d;

PRINT @sql;
EXEC master.sys.sp_executesql @sql, N'@sid varbinary(85)', @sid;

    在后一个示例中，如果您只希望将用户映射到命名登录的数据库，只需将第一个左连接更改为内连接。

    • 20

  2. 这个脚本是从一个提到的脚本稍微修改的，会做你正在寻找的。将“ThursdayClass”替换为您需要信息的登录名。 https://www.simple-talk.com/sql/sql-tools/the-sqlcmd-workbench/ 

        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 = 'ThursdayClass'
    • 7

  3. 尝试sp_dbpermissions。它可能会给你比你需要的更多的信息，但它会做你想做的事。

    一旦它安装运行这个。

    sp_dbpermissions @dbname = 'All', @LoginName = 'LoginName'

    公平警告目前它会进行“喜欢”匹配，因此如果其他登录名相似且匹配，那么您也会看到它们。例如MyLoginandMyLoginForThis都将匹配MyLogin. 如果这是一个问题，我有一个尚未发布的版本，您可以将其关闭。让我知道，我可以通过电子邮件发送给您。

    • 6

  4. 遗憾的是，您将不得不遍历所有数据库才能获取信息。您需要加入sys.database_principals到sys.server_principalsSID 上匹配的每个数据库。

    不要使用sp_msforeachdb，因为它有时会丢失数据库。

    • 4

  5. 这是一个powershell解决方案：

    import-module sqlps;

$s = new-object microsoft.sqlserver.management.smo.server '.'
foreach ($db in $s.Databases | where {$_.IsAccessible -eq $true}) {
   $u = $db.users | where {$_.Login -eq 'foobar'}
   if ($u -ne $null) { #login is mapped to a user in the db
       foreach ($role in $db.Roles) {
           if ($role.EnumMembers() -contains $u.Name) {
               $u | select parent, @{name="role";expression={$role.name}}, name
           }
       }
   }
}
    • 4

  6. 我正在寻找类似的答案，发现：https ://www.pythian.com/blog/httpconsultingblogs-emc-comjamiethomsonarchive20070209sql-server-2005_3a00_-view-all-permissions-_2800_2_2900_-aspx/ 。是的，它使用了可怕的 sp_MSforeachDB，但我认为那个家伙有时会受到不好的说唱...... ;-)

    我将在此处发布 SQL 以便轻松复制意大利面（我不为此而功劳，只是让它易于访问！）：

    DECLARE @DB_Users TABLE (DBName sysname, UserName sysname, LoginType sysname
, AssociatedRole varchar(max), create_date datetime, modify_date datetime)

INSERT @DB_Users
EXEC sp_MSforeachdb
'use [?]
SELECT ''?'' AS DB_Name,
case prin.name when ''dbo'' then prin.name + '' (''
    + (select SUSER_SNAME(owner_sid) from master.sys.databases where name =''?'') + '')''
    else prin.name end AS UserName,
    prin.type_desc AS LoginType,
    isnull(USER_NAME(mem.role_principal_id),'''') AS AssociatedRole, 
    create_date, modify_date
FROM sys.database_principals prin
LEFT OUTER JOIN sys.database_role_members mem
    ON prin.principal_id=mem.member_principal_id
WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00)
and prin.is_fixed_role <> 1 AND prin.name NOT LIKE ''##%'''

SELECT dbname, username, logintype, create_date, modify_date,
    STUFF((SELECT ',' + CONVERT(VARCHAR(500), associatedrole)
        FROM @DB_Users user2
        WHERE user1.DBName=user2.DBName AND user1.UserName=user2.UserName
        FOR XML PATH('')
    ),1,1,'') AS Permissions_user
FROM @DB_Users user1
WHERE user1.UserName = N'<put your login-name here!>'
GROUP BY dbname, username, logintype, create_date, modify_date
ORDER BY DBName, username
    • 1

  7. 下面的 Query 将返回请求的 DbName 的映射

    SELECT 'DbName', dbPri.name, dbPri1.name
FROM [DbName].sys.database_principals dbPri 
JOIN [DbName].sys.database_role_members dbRoleMem ON dbRoleMem.member_principal_id = 
dbPri.principal_id
JOIN [DbName].sys.database_principals dbPri1  ON dbPri1.principal_id = 
dbRoleMem.role_principal_id
WHERE dbPri.name != 'dbo'

    改进的查询如下

    declare @sql varchar(Max)

 set @sql = 'use ? SELECT ''?'', dbPri.name, dbPri1.name
 FROM sys.database_principals dbPri 
 JOIN sys.database_role_members dbRoleMem ON 
 dbRoleMem.member_principal_id = 
 dbPri.principal_id
 JOIN sys.database_principals dbPri1  ON dbPri1.principal_id = 
 dbRoleMem.role_principal_id
 WHERE dbPri.name != ''dbo'''

 EXEC sp_MSforeachdb @sql
    • -1

  8. 怎么样EXEC master..sp_msloginmappings？

    • -3

相关问题