SHARE
TWEET

Login with Users

SQLSoldier May 19th, 2016 78 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Declare @Login sysname,
  2.     @CurrID int,
  3.     @MaxID int,
  4.     @DBName sysname,
  5.     @SQL nvarchar(1000);
  6.  
  7. Declare @LogInfo Table (AcctID int not null identity(1, 1) primary key,
  8.                         AcctName sysname not null, -- Fully qualified Windows account name
  9.                         AcctType char(8) not null, -- Type of Windows account. Valid values are user or group
  10.                         Privilege char(9) not null, -- Access privilege for SQL Server. Valid values are admin, user, or null
  11.                         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
  12.                         PermissionPath sysname null, -- Group membership that allowed the account access
  13.                         SysID varbinary(85) null,
  14.                         PrincipalID int null,
  15.                         DefaultDB sysname null,
  16.                         IsDisabled bit not null default(0));
  17.  
  18. Declare @ServerRoles Table (SRoleID int identity(1, 1) not null primary key,
  19.                             AcctID int not null,
  20.                             RoleName sysname not null);
  21.  
  22. Declare @DBs Table (DBsID int identity(1, 1) not null primary key,
  23.                     DBName sysname not null);
  24.  
  25. Declare @DBRoles Table (DBRoleID int identity(1, 1) not null primary key,
  26.                     SysID varbinary(85) null,
  27.                     DBName sysname not null,
  28.                     RoleName sysname not null default('Public'),
  29.                     DBUser sysname null);
  30.  
  31. /* Enter Login to search for here */
  32. Set @Login = 'domain\user';
  33.  
  34. Set NoCount On;
  35.  
  36. Insert Into @LogInfo (AcctName, AcctType, Privilege, MappedLogin, PermissionPath)
  37. Exec xp_logininfo @Login, 'all';
  38.  
  39. If Exists (Select 1 From @LogInfo)
  40.   Begin
  41.     Update @LogInfo
  42.     Set PermissionPath = MappedLogin
  43.     Where PermissionPath Is Null;
  44.  
  45.     Update LI
  46.     Set SysID = SID, PrincipalID = principal_id, DefaultDB = default_database_name, IsDisabled = is_disabled
  47.     From @LogInfo LI
  48.     Inner Join sys.server_principals SP On SP.name = LI.PermissionPath;
  49.  
  50.     Insert Into @ServerRoles (AcctID, RoleName)
  51.     Select LI.AcctID, SP.name
  52.     From @LogInfo LI
  53.     Inner Join sys.server_role_members RM On RM.member_principal_id = LI.PrincipalID
  54.     Inner Join sys.server_principals SP On SP.principal_id = RM.role_principal_id
  55.     Where SP.type = 'R';
  56.  
  57.     Insert Into @DBs (DBName)
  58.     Select name
  59.     From sys.databases;
  60.  
  61.     Select @MaxID = Max(DBsID), @CurrID = 1
  62.     From @DBs;
  63.  
  64.     While @CurrID <= @MaxID
  65.       Begin
  66.         Select @DBName = DBName
  67.         From @DBs
  68.         Where DBsID = @CurrID;
  69.  
  70.         Set @SQL = 'Select ''' + @DBName + ''', IsNull(DRP.name, ''Public''), DP.sid, DP.name
  71.                     From ' + quotename(@DBName) + '.sys.database_principals DP
  72.                     Left Join ' + quotename(@DBName) + '.sys.database_role_members RM On RM.member_principal_id = DP.principal_id
  73.                     Left Join ' + quotename(@DBName) + '.sys.database_principals DRP On DRP.principal_id = RM.role_principal_id
  74.                     Where (DRP.type = ''R'' Or DRP.type Is Null)
  75.                     And DP.Type In (''U'', ''G'');';
  76.  
  77.         Insert Into @DBRoles (DBName, RoleName, SysID, DBUser)
  78.         Exec sp_executesql @SQL;
  79.  
  80.         Set @CurrID = @CurrID + 1;
  81.       End
  82.  
  83.     Select AcctName As [Account Name], AcctType As [Account Type], Privilege As [Highest Privilege], PermissionPath As [Parent Login], DefaultDB As [Default DB]
  84.     From @LogInfo;
  85.  
  86.     If Exists (Select 1 From @ServerRoles SR
  87.                 Inner Join @LogInfo LI On LI.AcctID = SR.AcctID)
  88.         Or Exists (Select 1 From @LogInfo LI
  89.                     Inner Join sys.server_principals SP On SP.[name] = LI.PermissionPath Or SP.principal_id = LI.PrincipalID
  90.                     Inner Join sys.server_permissions PM On PM.grantee_principal_id = LI.PrincipalID
  91.                     Where SP.type In ('G', 'U')
  92.                     And PM.state = 'G'
  93.                     And PM.type <> 'COSQ')
  94.       Begin
  95.         Select LI.PermissionPath As [Parent Login], RoleName As [Server Role]
  96.         From @ServerRoles SR
  97.         Inner Join @LogInfo LI On LI.AcctID = SR.AcctID
  98.         Union
  99.         Select SP.[name], Cast(PM.permission_name as sysname) Collate SQL_Latin1_General_CP1_CI_AS
  100.         From @LogInfo LI
  101.         Inner Join sys.server_principals SP On SP.[name] = LI.PermissionPath Or SP.principal_id = LI.PrincipalID
  102.         Inner Join sys.server_permissions PM On PM.grantee_principal_id = LI.PrincipalID
  103.         Where SP.type In ('G', 'U')
  104.         And PM.state = 'G'
  105.         And PM.type <> 'COSQ';
  106.       End
  107.     Else
  108.       Begin
  109.         Select 'None' As [Parent Login], 'None' As [Server Role];
  110.       End
  111.  
  112.     If Exists (Select 1 From @DBRoles DR
  113.                 Inner Join @LogInfo LI On LI.SysID = DR.SysID)
  114.       Begin
  115.         Select DR.DBName As [Database], LI.PermissionPath As [Parent Login], RoleName As [Database Role], DBUser As [Database user]
  116.         From @DBRoles DR
  117.         Inner Join @LogInfo LI On LI.SysID = DR.SysID;
  118.       End
  119.     Else
  120.       Begin
  121.         Select 'None' As [Database], 'None' As [Parent Login], 'None' As [Database Role], 'None' As [Database user]
  122.         From @LogInfo;
  123.       End
  124.  End
  125. Else
  126.   Begin
  127.     RaisError('No login mappings found for this account.', 16, 1);
  128.   End
  129.  
  130. Set NoCount Off;
RAW Paste Data
Pastebin PRO Summer Special!
Get 60% OFF on Pastebin PRO accounts!
Top