sql-server - SQL Server - 查找服务器上所有数据库的用户/登录名的所有权限/访问权限

问题描述

案子：

用户正在离开组织，我想查看此人对服务器上所有数据库的所有安全性。

背景：

有人问过这样一个只针对一个数据库的问题。
这个问题与这个问题的不同之处在于它要求在所有数据库中找到安全性，而不仅仅是一个。此外，这要求能够指定特定用户和/或特定登录名。
下面的代码来自 Sean Rose 的答案，因为它通过一些改进修改了批准的答案。
我添加了变量名称和功能，以便能够专门搜索登录名和/或用户，或者您可以将变量留空。

所需的修改：

我希望将其修改为包含服务器上的所有数据库。

我不知道如何查看服务器上的所有数据库。

            DECLARE @DatabaseUserName VARCHAR(50)  -- ='user'
          , @LoginName        VARCHAR(50)  -- ='login'



    SELECT      [UserType]         = CASE princ.[type] WHEN 'S' THEN 'SQL User'
                                         WHEN 'U' THEN 'Windows User'
                                         WHEN 'G' THEN 'Windows Group' END
              , [DatabaseUserName] = princ.[name]
              , [LoginName]        = ulogin.[name]
              , [Role]             = NULL
              , [PermissionType]   = perm.[permission_name]
              , [PermissionState]  = perm.[state_desc]
              , [ObjectType]       = CASE perm.[class] WHEN 1 THEN obj.[type_desc] -- Schema-contained objects
                                         ELSE perm.[class_desc]                    -- Higher-level objects
                                     END
              , [Schema]           = objschem.[name]
              , [ObjectName]       = CASE perm.[class] WHEN 3 THEN permschem.[name] -- Schemas
                                         WHEN 4 THEN imp.[name]                     -- Impersonations
                                         ELSE OBJECT_NAME (perm.[major_id])         -- General objects
                                     END
              , [ColumnName]       = col.[name]
    FROM
        --Database user
                sys.database_principals  AS princ
    --Login accounts
    LEFT JOIN   sys.server_principals    AS ulogin ON ulogin.[sid] = princ.[sid]
    --Permissions
    LEFT JOIN   sys.database_permissions AS perm ON perm.[grantee_principal_id] = princ.[principal_id]
    LEFT JOIN   sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
    LEFT JOIN   sys.objects              AS obj ON obj.[object_id] = perm.[major_id]
    LEFT JOIN   sys.schemas              AS objschem ON objschem.[schema_id] = obj.[schema_id]
    --Table columns
    LEFT JOIN   sys.columns              AS col ON col.[object_id] = perm.[major_id]
                                                   AND   col.[column_id] = perm.[minor_id]
    --Impersonations
    LEFT JOIN   sys.database_principals  AS imp ON imp.[principal_id] = perm.[major_id]
    WHERE       princ.[type] IN ( 'S', 'U', 'G' )
                -- No need for these system accounts
                AND princ.[name] NOT IN ( 'sys', 'INFORMATION_SCHEMA' )
                AND (princ.[name] = @DatabaseUserName OR  @DatabaseUserName IS NULL)            
                AND (ulogin.[name] = @LoginName OR  @LoginName IS NULL)
    UNION

    --2) List all access provisioned to a SQL user or Windows user/group through a database or application role
    SELECT      [UserType]         = CASE membprinc.[type] WHEN 'S' THEN 'SQL User'
                                         WHEN 'U' THEN 'Windows User'
                                         WHEN 'G' THEN 'Windows Group' END
              , [DatabaseUserName] = membprinc.[name]
              , [LoginName]        = ulogin.[name]
              , [Role]             = roleprinc.[name]
              , [PermissionType]   = perm.[permission_name]
              , [PermissionState]  = perm.[state_desc]
              , [ObjectType]       = CASE perm.[class] WHEN 1 THEN obj.[type_desc] -- Schema-contained objects
                                         ELSE perm.[class_desc]                    -- Higher-level objects
                                     END
              , [Schema]           = objschem.[name]
              , [ObjectName]       = CASE perm.[class] WHEN 3 THEN permschem.[name] -- Schemas
                                         WHEN 4 THEN imp.[name]                     -- Impersonations
                                         ELSE OBJECT_NAME (perm.[major_id])         -- General objects
                                     END
              , [ColumnName]       = col.[name]
    FROM
        --Role/member associations
                sys.database_role_members AS members
    --Roles
    JOIN        sys.database_principals   AS roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
    --Role members (database users)
    JOIN        sys.database_principals   AS membprinc ON membprinc.[principal_id] = members.[member_principal_id]
    --Login accounts
    LEFT JOIN   sys.server_principals     AS ulogin ON ulogin.[sid] = membprinc.[sid]
    --Permissions
    LEFT JOIN   sys.database_permissions  AS perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
    LEFT JOIN   sys.schemas               AS permschem ON permschem.[schema_id] = perm.[major_id]
    LEFT JOIN   sys.objects               AS obj ON obj.[object_id] = perm.[major_id]
    LEFT JOIN   sys.schemas               AS objschem ON objschem.[schema_id] = obj.[schema_id]
    --Table columns
    LEFT JOIN   sys.columns               AS col ON col.[object_id] = perm.[major_id]
                                                    AND   col.[column_id] = perm.[minor_id]
    --Impersonations
    LEFT JOIN   sys.database_principals   AS imp ON imp.[principal_id] = perm.[major_id]
    WHERE       membprinc.[type] IN ( 'S', 'U', 'G' )
                -- No need for these system accounts
                AND membprinc.[name] NOT IN ( 'sys', 'INFORMATION_SCHEMA' )
                AND (membprinc.[name] = @DatabaseUserName OR  @DatabaseUserName IS NULL)
                AND (ulogin.[name] = @LoginName OR  @LoginName IS NULL)
    UNION

    --3) List all access provisioned to the public role, which everyone gets by default
    SELECT      [UserType]         = '{All Users}'
              , [DatabaseUserName] = '{All Users}'
              , [LoginName]        = '{All Users}'
              , [Role]             = roleprinc.[name]
              , [PermissionType]   = perm.[permission_name]
              , [PermissionState]  = perm.[state_desc]
              , [ObjectType]       = CASE perm.[class] WHEN 1 THEN obj.[type_desc] -- Schema-contained objects
                                         ELSE perm.[class_desc]                    -- Higher-level objects
                                     END
              , [Schema]           = objschem.[name]
              , [ObjectName]       = CASE perm.[class] WHEN 3 THEN permschem.[name] -- Schemas
                                         WHEN 4 THEN imp.[name]                     -- Impersonations
                                         ELSE OBJECT_NAME (perm.[major_id])         -- General objects
                                     END
              , [ColumnName]       = col.[name]
    FROM
        --Roles
                sys.database_principals  AS roleprinc
    --Role permissions
    LEFT JOIN   sys.database_permissions AS perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
    LEFT JOIN   sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
    --All objects
    JOIN        sys.objects              AS obj ON obj.[object_id] = perm.[major_id]
    LEFT JOIN   sys.schemas              AS objschem ON objschem.[schema_id] = obj.[schema_id]
    --Table columns
    LEFT JOIN   sys.columns              AS col ON col.[object_id] = perm.[major_id]
                                                   AND   col.[column_id] = perm.[minor_id]
    --Impersonations
    LEFT JOIN   sys.database_principals  AS imp ON imp.[principal_id] = perm.[major_id]
    WHERE       roleprinc.[type] = 'R'
                AND roleprinc.[name] = 'public'
                AND obj.[is_ms_shipped] = 0
                AND (roleprinc.[name] = @DatabaseUserName OR  @DatabaseUserName IS NULL)


    ORDER BY    [UserType]
              , [DatabaseUserName]
              , [LoginName]
              , [Role]
              , [Schema]
              , [ObjectName]
              , [ColumnName]
              , [PermissionType]
              , [PermissionState]
              , [ObjectType];

标签： sql-server

解决方案

我可以想到两种方法可以实现这一目标。

第一种方法是使用临时表并对所有数据库执行权限脚本，如下所示：

DECLARE @DatabaseName VARCHAR(50);
DECLARE @SqlCommand NVARCHAR(MAX);
DECLARE @DatabaseUserName VARCHAR(50); -- ='user'
DECLARE @LoginName VARCHAR(50); -- ='login'

CREATE TABLE #TEMP_OVERVIEW
(
  DatabaseName     VARCHAR(128)  NOT NULL
, UserType         VARCHAR(13)   NULL
, DatabaseUserName NVARCHAR(128) NOT NULL
, LoginName        NVARCHAR(128) NULL
, Role             NVARCHAR(128) NULL
, PermissionType   NVARCHAR(128) NULL
, PermissionState  NVARCHAR(60)  NULL
, ObjectType       NVARCHAR(60)  NULL
, [Schema]         sys.sysname   NULL
, ObjectName       NVARCHAR(128) NULL
, ColumnName       sys.sysname   NULL
);

DECLARE db_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT
      name
FROM  master.sys.databases
WHERE name NOT IN ('master', 'msdb', 'model', 'tempdb')
      AND state_desc = 'online';

OPEN db_cursor;

FETCH NEXT FROM db_cursor
INTO
  @DatabaseName;

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT
    @SqlCommand = N'USE ' + @DatabaseName + N';' + N'
INSERT INTO #TEMP_OVERVIEW
SELECT '''        + @DatabaseName + N''', t.*
FROM (

...在此处插入您的脚本...

) AS t
' ;

  EXEC sp_executesql @SqlCommand;

  FETCH NEXT FROM db_cursor
  INTO
    @DatabaseName;
END;

CLOSE db_cursor;
DEALLOCATE db_cursor;

SELECT
      *
FROM  #TEMP_OVERVIEW
WHERE DatabaseUserName = @DatabaseUserName
      AND LoginName = @LoginName;

DROP TABLE #TEMP_OVERVIEW;

第二种方法是使用sp_MSforeachdb. 但在进一步解释这一点之前，我必须提醒您，因为它是一个全局游标，并且多年来被微软认为已弃用。它也是无证的/不受支持的，所以如果你用搞砸了一些东西sp_MSforeachdb，你就靠自己了。例如，使用：

EXEC sp_MSforeachdb 'USE ?; SELECT ''?'' AS DATABASE_NAME, * FROM INFORMATION_SCHEMA.TABLES'

这会给你：

以此类推，适用于您服务器上的所有数据库，因此您可以看到很可能将您的权限脚本合并到sp_MSforeachdb调用中。

sql-server - SQL Server - 查找服务器上所有数据库的用户/登录名的所有权限/访问权限

问题描述

解决方案

推荐阅读