SHARE
TWEET

All Logins With DB Users

SQLSoldier May 19th, 2016 59 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Declare @Login sysname,
  2.     @LoginType char(1),
  3.     @CurrID int,
  4.     @MaxID int,
  5.     @DBName sysname,
  6.     @SQL nvarchar(1000);
  7.  
  8. Declare @Logins Table (LoginID int not null identity(1, 1) primary key,
  9.                         AcctName sysname not null,
  10.                         LoginType char(1))
  11.  
  12. Declare @LogInfo Table (AcctID int not null identity(1, 1) primary key,
  13.                         AcctName sysname not null, -- Fully qualified Windows account name
  14.                         AcctType varchar(50) not null, -- Type of Windows account. Valid values are user or group
  15.                         Privilege char(9) not null, -- Access privilege for SQL Server. Valid values are admin, user, or null
  16.                         MappedLogin sysname not null, -- For user accounts that have user privilege, mapped login name shows the mapped login name that SQL Server tries to use when logging in with this account by using the mapped rules with the domain name added before it
  17.                         PermissionPath sysname null, -- Group membership that allowed the account access
  18.                         SysID varbinary(85) null,
  19.                         PrincipalID int null,
  20.                         DefaultDB sysname null,
  21.                         IsDisabled bit not null default(0));
  22.  
  23. Declare @ServerRoles Table (SRoleID int identity(1, 1) not null primary key,
  24.                             AcctID int not null,
  25.                             RoleName sysname not null);
  26.  
  27. Declare @DBs Table (DBsID int identity(1, 1) not null primary key,
  28.                     DBName sysname not null);
  29.  
  30. Declare @DBRoles Table (DBRoleID int identity(1, 1) not null primary key,
  31.                     SysID varbinary(85) null,
  32.                     DBName sysname not null,
  33.                     RoleName sysname not null default('Public'),
  34.                     DBUser sysname null);
  35.  
  36. Set NoCount On;
  37.  
  38. Insert Into @Logins (AcctName, LoginType)
  39. Select name, type
  40. From sys.server_principals
  41. Where type In ('S', 'U', 'G', 'C')
  42. And name not like N'##%';
  43.  
  44. Select @MaxID = Max(LoginID),
  45.     @CurrID = 1
  46. From @Logins;
  47.  
  48. While @CurrID <= @MaxID
  49.   Begin
  50.     Select @Login = AcctName,
  51.         @LoginType = LoginType
  52.     From @Logins
  53.     Where LoginID = @CurrID;
  54.  
  55.     If @LoginType In ('U', 'G')
  56.       Begin
  57.         Begin Try
  58.             Insert Into @LogInfo (AcctName, AcctType, Privilege, MappedLogin, PermissionPath)
  59.             Exec xp_logininfo @Login, 'all';
  60.         End Try
  61.         Begin Catch
  62.             Insert Into @LogInfo (AcctName, AcctType, Privilege, MappedLogin, PermissionPath)
  63.             Select @Login,
  64.                 Case @LoginType When 'U' Then 'User'
  65.                     When 'G' Then 'Group'
  66.                 End,
  67.                 Case IS_SRVROLEMEMBER(N'sysadmin', @Login) When 0 Then 'user'
  68.                     When 1 Then 'admin'
  69.                 End,
  70.                 @Login,
  71.                 Null;
  72.         End Catch
  73.       End
  74.     Else
  75.       Begin
  76.         Insert Into @LogInfo (AcctName, AcctType, Privilege, MappedLogin, PermissionPath)
  77.         Select @Login,
  78.             Case @LoginType When 'S' Then 'SQL User'
  79.                 When 'C' Then 'Certificate Mapped User'
  80.             End,
  81.             Case IS_SRVROLEMEMBER(N'sysadmin', @Login) When 0 Then 'user'
  82.                     When 1 Then 'admin'
  83.                 End,
  84.             @Login,
  85.             Null;
  86.       End
  87.  
  88.     Set @CurrID = @CurrID + 1;
  89.   End
  90.  
  91. If Exists (Select 1 From @LogInfo)
  92.   Begin
  93.     Update @LogInfo
  94.     Set PermissionPath = MappedLogin
  95.     Where PermissionPath Is Null;
  96.  
  97.     Update LI
  98.     Set SysID = SID, PrincipalID = principal_id, DefaultDB = default_database_name, IsDisabled = is_disabled
  99.     From @LogInfo LI
  100.     Inner Join sys.server_principals SP On SP.name = LI.PermissionPath;
  101.  
  102.     Insert Into @ServerRoles (AcctID, RoleName)
  103.     Select LI.AcctID, SP.name
  104.     From @LogInfo LI
  105.     Inner Join sys.server_role_members RM On RM.member_principal_id = LI.PrincipalID
  106.     Inner Join sys.server_principals SP On SP.principal_id = RM.role_principal_id
  107.     Where SP.type = 'R';
  108.  
  109.     Insert Into @DBs (DBName)
  110.     Select name
  111.     From sys.databases;
  112.  
  113.     Select @MaxID = Max(DBsID),
  114.         @CurrID = 1
  115.     From @DBs;
  116.  
  117.     While @CurrID <= @MaxID
  118.       Begin
  119.         Select @DBName = DBName
  120.         From @DBs
  121.         Where DBsID = @CurrID;
  122.  
  123.         Set @SQL = 'Select ''' + @DBName + ''', IsNull(DRP.name, ''Public''), DP.sid, DP.name
  124.                     From ' + quotename(@DBName) + '.sys.database_principals DP
  125.                     Left Join ' + quotename(@DBName) + '.sys.database_role_members RM On RM.member_principal_id = DP.principal_id
  126.                     Left Join ' + quotename(@DBName) + '.sys.database_principals DRP On DRP.principal_id = RM.role_principal_id
  127.                     Where (DRP.type = ''R'' Or DRP.type Is Null)
  128.                     And DP.Type In (''U'', ''G'', ''S'', ''C'');';
  129.  
  130.         Insert Into @DBRoles (DBName, RoleName, SysID, DBUser)
  131.         Exec sp_executesql @SQL;
  132.  
  133.         Set @CurrID = @CurrID + 1;
  134.       End
  135.  
  136.     Select AcctName As [Account Name], AcctType As [Account Type], Privilege As [Highest Privilege], PermissionPath As [Parent Login], DefaultDB As [Default DB]
  137.     From @LogInfo;
  138.  
  139.     If Exists (Select 1 From @ServerRoles SR
  140.                 Inner Join @LogInfo LI On LI.AcctID = SR.AcctID)
  141.         Or Exists (Select 1 From @LogInfo LI
  142.                     Inner Join sys.server_principals SP On SP.[name] = LI.PermissionPath Or SP.principal_id = LI.PrincipalID
  143.                     Inner Join sys.server_permissions PM On PM.grantee_principal_id = LI.PrincipalID
  144.                     Where SP.type In ('G', 'U', 'S', 'C')
  145.                     And PM.state = 'G'
  146.                     And PM.type <> 'COSQ')
  147.       Begin
  148.         Select LI.AcctName As [Account Name], LI.PermissionPath As [Parent Login], RoleName As [Server Role]
  149.         From @ServerRoles SR
  150.         Inner Join @LogInfo LI On LI.AcctID = SR.AcctID
  151.         Union
  152.         Select LI.AcctName As [Account Name], SP.[name], Cast(PM.permission_name as sysname) Collate SQL_Latin1_General_CP1_CI_AS
  153.         From @LogInfo LI
  154.         Inner Join sys.server_principals SP On SP.[name] = LI.PermissionPath Or SP.principal_id = LI.PrincipalID
  155.         Inner Join sys.server_permissions PM On PM.grantee_principal_id = LI.PrincipalID
  156.         Where SP.type In ('G', 'U', 'S', 'C')
  157.         And PM.state = 'G'
  158.         And PM.type <> 'COSQ';
  159.       End
  160.  
  161.     If Exists (Select 1 From @DBRoles DR
  162.                 Inner Join @LogInfo LI On LI.SysID = DR.SysID)
  163.       Begin
  164.         Select LI.AcctName As [Account Name], DR.DBName As [Database], LI.PermissionPath As [Parent Login], RoleName As [Database Role], DBUser As [Database user]
  165.         From @DBRoles DR
  166.         Inner Join @LogInfo LI On LI.SysID = DR.SysID;
  167.       End
  168.  End
  169.  
  170. Set NoCount Off;
RAW Paste Data
Top