sql-server - 是否有 SQL Server 实用程序可以生成所有服务器登录名的大列表及其访问概述？

Question

我想知道是否有一个实用程序可以列出特定服务器上的服务器登录及其访问权限。

这将是我通过右键单击每个用户获得的信息 -> 属性 -> 用户映射
但不是为每个用户手动执行该过程，它只是列出信息。

例如:

MyDomain\John
 - Server Roles
   - sysadmin
 - MyDatabase1
   - Roles
     db_reader
     public
 - MyDatabase2
   - Roles
     db_reader
     public
MyDomain\Steve
 - MyDatabase1
   - Roles
     db_reader
     public

Answer 1

我修改了 sp_helplogins 系统存储过程以返回接近您要查找的内容。

我无法正确粘贴脚本，所以这里是 link到脚本。

DECLARE    @LoginNamePattern     sysname    = NULL

set nocount on

declare
        @exec_stmt nvarchar(3550)

declare
       @CountSkipPossUsers             int
      ,@Int1                           int

declare
       @c10DBName                      sysname
      ,@c10DBStatus                    int
      ,@c10DBSID                       varbinary(85)

declare
       @charMaxLenLoginName            varchar(11)
      ,@charMaxLenDBName               varchar(11)
      ,@charMaxLenUserName             varchar(11)

declare
       @DBOptLoading                   int   --0x0020      32  "DoNotRecover"
      ,@DBOptPreRecovery               int   --0x0040      64
      ,@DBOptRecovering                int   --0x0080     128

      ,@DBOptSuspect                   int   --0x0100     256  ("not recovered")
      ,@DBOptOffline                   int   --0x0200     512
      ,@DBOptDBOUseOnly                int   --0x0800    2048

      ,@DBOptSingleUser                int   --0x1000    4096


-------------  create work holding tables  ----------------
-- Create temp tables before any DML to ensure dynamic


CREATE TABLE #tb1_UA
   (
    LoginName                       sysname     collate database_default NOT Null
   ,DBName                          sysname     collate database_default NOT Null
   ,UserName                        sysname     collate database_default NOT Null
   )




---------------  Cursor, for DBNames  -------------------


declare ms_crs_10_DB
   Cursor local static For
select
             name ,status ,sid
      from
             master.dbo.sysdatabases



OPEN ms_crs_10_DB


-----------------  LOOP 10:  thru Databases  ------------------


WHILE (10 = 10)
   begin    --LOOP 10: thru Databases


   FETCH
             next
      from
             ms_crs_10_DB
      into
             @c10DBName
            ,@c10DBStatus
            ,@c10DBSID


   IF (@@fetch_status <> 0)
      begin
      deallocate ms_crs_10_DB
      BREAK
      end


--------------------  Okay if we peek inside this DB now?


   IF (     @c10DBStatus & @DBOptDBOUseOnly  > 0
       AND  @c10DBSID                       <> suser_sid()
      )
      begin
      select @CountSkipPossUsers = @CountSkipPossUsers + 1
      CONTINUE
      end


   IF (@c10DBStatus & @DBOptSingleUser  > 0)
      begin

      select    @Int1 = count(*)
         from   sys.dm_exec_requests
         where  session_id <> @@spid
         and    database_id = db_id(@c10DBName)

      IF (@Int1 > 0)
         begin
         select @CountSkipPossUsers = @CountSkipPossUsers + 1
         CONTINUE
         end
      end


   IF (@c10DBStatus &
         (
           @DBOptLoading
         | @DBOptRecovering
         | @DBOptSuspect
         | @DBOptPreRecovery
         )
               > 0
      )
      begin
      select @CountSkipPossUsers = @CountSkipPossUsers + 1
      CONTINUE
      end


   IF (@c10DBStatus &
         (
           @DBOptOffline
         )
               > 0
      )
      begin
      --select @CountSkipPossUsers = @CountSkipPossUsers + 1
      CONTINUE
      end

    IF (has_dbaccess(@c10DBName) <> 1)
      begin
      raiserror(15622,-1,-1, @c10DBName)
      CONTINUE
      end



---------------------  Add the User info to holding table.
    select @exec_stmt = '
   INSERT    #tb1_UA
            (
             DBName
            ,LoginName
            ,UserName
            )
      select

             N' + quotename(@c10DBName, '''') + '
            ,l.name
            ,u2.name
         from
             ' + quotename(@c10DBName, '[')+ '.sys.database_role_members m
            ,' + quotename(@c10DBName, '[')+ '.sys.database_principals u1
            ,' + quotename(@c10DBName, '[')+ '.sys.database_principals u2
            ,sys.server_principals l
         where
             u1.sid = l.sid
         and m.member_principal_id = u1.principal_id
         and m.role_principal_id = u2.principal_id' +
            case 
            when @LoginNamePattern is null
            then ''
            else ' and ( l.name = N' + quotename(@LoginNamePattern , '''') + '
                or l.name = N' + quotename(@LoginNamePattern , '''') + ')'
            end

   EXECUTE(@exec_stmt)

   end --loop 10


------------  Optimize UA report column display widths  -----------


select
             @charMaxLenLoginName   =
                  convert ( varchar
                           ,isnull ( max(datalength(LoginName)) ,9)
                          )
            ,@charMaxLenDBName      =
                  convert ( varchar
                           ,isnull ( max(datalength(DBName)) ,6)
                          )
            ,@charMaxLenUserName    =
                  convert ( varchar
                           ,isnull ( max(datalength(UserName)) ,8)
                          )
      from
             #tb1_UA



------------  Print out the UserOrAlias report  ------------

EXEC(
'
set nocount off


select
          ''LoginName''    = substring (LoginName  ,1 ,'
                                       + @charMaxLenLoginName  + ')

         ,''DBName''       = substring (DBName     ,1 ,'
                                       + @charMaxLenDBName     + ')

         ,''MemberOf''     = substring (UserName   ,1 ,'
                                       + @charMaxLenUserName   + ')

   from
          #tb1_UA
   order by
          1 ,2 ,3


Set nocount on
'
)

DROP Table #tb1_UA