USE TEMPORARY
GO
DROP FUNCTION dbo.GetActiveDirectoryName
GO
CREATE FUNCTION dbo.GetActiveDirectoryName (@Input VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
RETURN(SELECT CASE ps2 WHEN 'ISC' THEN 'infosolco.net'
WHEN 'FIRSTAMDATA' THEN 'firstamdata.net'
WHEN 'ISCR' THEN 'iscresource.com'
WHEN 'ISCP' THEN 'infosolco.com '
WHEN 'FLL' THEN 'FLL.firstamdata.com'
WHEN 'ANA' THEN 'ANA.firstamdata.net'
END
FROM (SELECT CHARINDEX('\', @Input) AS ps)p
CROSS APPLY(SELECT CASE WHEN ps > 0 THEN SUBSTRING(@Input, 1, ps -1) END AS ps2)p2)
END
GO
RETURN
IF OBJECT_ID('Temporary.dbo.GroupMembers') IS NOT NULL
DROP TABLE TEMPORARY.dbo.GroupMembers
IF OBJECT_ID('Temporary.dbo.Users') IS NOT NULL
DROP TABLE TEMPORARY.dbo.Users
IF OBJECT_ID('Temporary.dbo.XPlogins') IS NOT NULL
DROP TABLE TEMPORARY.dbo.XPlogins
IF OBJECT_ID('Temporary.dbo.AllDatabasePrincipals') IS NOT NULL
DROP TABLE TEMPORARY.dbo.AllDatabasePrincipals
IF OBJECT_ID('Temporary.dbo.AllDatabaseUsers') IS NOT NULL
DROP TABLE TEMPORARY.dbo.AllDatabaseUsers
GO
IF OBJECT_ID('Temporary.dbo.GroupMembers') IS NULL
SELECT p.name AS GroupName,
IS_SRVROLEMEMBER('sysadmin', p.name) AS SysAdmin,
b.MemberName
INTO TEMPORARY.dbo.GroupMembers
FROM sys.server_principals p (NOLOCK)
OUTER APPLY Util.[AD].[GetGroupMembers](TEMPORARY.dbo.GetActiveDirectoryName(p.NAME), p.name, 0) b
WHERE p.TYPE = 'G'
AND p.NAME NOT LIKE 'NT SERVICE\%'
AND p.NAME NOT LIKE 'BUILTIN\%'
-- BUILTIN\Administrators
IF OBJECT_ID('Temporary.dbo.XPlogins') IS NULL
BEGIN
DECLARE @IsSysAdmin INT
SELECT @IsSysAdmin = IS_SRVROLEMEMBER('sysadmin', name)
FROM sys.server_principals(NOLOCK)
WHERE NAME = 'BUILTIN\Administrators'
IF @@ROWCOUNT = 1
BEGIN
CREATE TABLE TEMPORARY.dbo.XPlogins (AccountName SYSNAME NULL,
[type] VARCHAR(8) NULL,
privilege VARCHAR(9) NULL,
MappedLoginName SYSNAME NULL,
PermissionPath SYSNAME)
INSERT TEMPORARY.dbo.XPlogins
EXEC xp_logininfo
@acctname = 'BUILTIN\Administrators',
@option = 'members'
INSERT TEMPORARY.dbo.GroupMembers
SELECT a.PermissionPath AS GroupName,
@IsSysAdmin AS SysAdmin,
b.MemberName
FROM TEMPORARY.dbo.XPlogins a
OUTER APPLY Util.[AD].[GetGroupMembers](TEMPORARY.dbo.GetActiveDirectoryName(a.AccountName), a.AccountName, 0) b
WHERE a.[type] = 'group'
UNION ALL
SELECT PermissionPath AS GroupName,
@IsSysAdmin AS SysAdmin,
AccountName AS MemberName
FROM TEMPORARY.dbo.XPlogins a
WHERE a.[type] = 'user'
END
END
IF OBJECT_ID('Temporary.dbo.Users') IS NULL
SELECT p.NAME AS FullName,
d.C1 AS DOMAIN,
d.C2 AS UserName,
IS_SRVROLEMEMBER('sysadmin', p.name) AS SysAdmin
INTO TEMPORARY.dbo.Users
FROM sys.server_principals p (NOLOCK)
CROSS APPLY Util.dbo.ParseDelimitedColumns16(p.name, '\') d
WHERE TYPE = 'U'
AND NAME NOT LIKE 'NT AUTHORITY%'
-- GET ALL DATABASE USERS
IF OBJECT_ID('Temporary.dbo.AllDatabasePrincipals') IS NULL
BEGIN
CREATE TABLE TEMPORARY.dbo.AllDatabasePrincipals ([DatabaseName] VARCHAR(128) NOT NULL,
[PrincipalName] VARCHAR(128) NOT NULL,
[PrincipalType] CHAR(1) NOT NULL,
[AnyAccess] INT NULL,
[CONNECT] INT NULL,
[db_owner] INT NULL,
[RoleCount] INT NULL,
[Roles] VARCHAR(MAX) NULL)
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = 'INSERT Temporary.dbo.AllDatabasePrincipals
' + Util.dbo.StringConcat('SELECT ''' + name + ''' AS DatabaseName,
name COLLATE SQL_Latin1_General_CP1_CI_AS AS PrincipalName,
Type COLLATE SQL_Latin1_General_CP1_CI_AS AS PrincipalType,
has_perms_by_name(''' + name + ''', ''DATABASE'', ''ANY'') AS AnyAccess,
has_perms_by_name(''' + name + ''', ''DATABASE'', ''CONNECT'') AS [Connect],
b.db_owner,
b.RoleCount,
b.Roles
FROM ' + name + '.sys.database_principals a (NOLOCK)
INNER JOIN (SELECT mp.name COLLATE SQL_Latin1_General_CP1_CI_AS AS MemberName,
SUM(CASE
WHEN rp.name COLLATE SQL_Latin1_General_CP1_CI_AS = ''db_owner'' THEN 1
ELSE 0
END) AS db_owner,
COUNT(DISTINCT rp.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS RoleCount,
Util.dbo.StringConcat(DISTINCT rp.name COLLATE SQL_Latin1_General_CP1_CI_AS, '', '') AS Roles
FROM ' + name + '.sys.database_role_members a (NOLOCK)
INNER JOIN ' + name + '.sys.database_principals rp (NOLOCK) ON rp.principal_id = a.role_principal_id
INNER JOIN ' + name
+ '.sys.database_principals AS mp (NOLOCK) ON mp.principal_id = a.member_principal_id
GROUP BY mp.name COLLATE SQL_Latin1_General_CP1_CI_AS) b ON A.NAME COLLATE SQL_Latin1_General_CP1_CI_AS = B.MemberName COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE type COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN (''R'')
AND name COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''SYS'', ''##MS_PolicyEventProcessingLogin##'', ''##MS_PolicyTsqlExecutionLogin##'', ''MS_DataCollectorInternalUser'')',
'
UNION ALL
')
FROM sys.databases (NOLOCK)
--EXEC Util.dbo.PrintLargeText @SQL
EXEC (@SQL)
IF OBJECT_ID('Temporary.dbo.AllDatabaseUsers') IS NULL
BEGIN
SELECT a.DatabaseName,
a.PrincipalName,
a.PrincipalType,
a.AnyAccess,
a.[Connect],
a.db_owner,
a.RoleCount,
a.Roles,
COALESCE(gm.SysAdmin, u.SysAdmin, p.SysAdmin) AS SysAdmin,
COALESCE(gm.MemberName, p.Name, un.UserName) AS MemberName
INTO TEMPORARY.dbo.AllDatabaseUsers
FROM TEMPORARY.dbo.AllDatabasePrincipals a
LEFT OUTER JOIN TEMPORARY.dbo.GroupMembers gm ON a.PrincipalType = 'G'
AND gm.GroupName = a.PrincipalName
LEFT OUTER JOIN TEMPORARY.dbo.Users u ON a.PrincipalType = 'U'
AND u.UserName = a.PrincipalName
LEFT OUTER JOIN (SELECT Name,
IS_SRVROLEMEMBER('sysadmin', p.name) AS SysAdmin
FROM sys.server_principals p (NOLOCK)
WHERE TYPE = 'S') p ON a.PrincipalType = 'S'
AND p.NAME = A.PrincipalName
OUTER APPLY (SELECT TOP 1
Field AS UserName
FROM Util.dbo.ParseDelimited(a.PrincipalName, '\')
ORDER BY FieldNum DESC) un
END
END
-- List of users
SELECT UserName,
MAX(SysAdmin) AS SysAdmin,
COUNT(DISTINCT GroupName) AS GroupCount,
ISNULL(Util.dbo.StringConcat(DISTINCT GroupName, ', '), '') AS BelongedGroups
FROM (SELECT MemberName AS UserName,
SysAdmin,
GroupName
FROM TEMPORARY.dbo.GroupMembers
UNION ALL
SELECT UserName,
SysAdmin,
NULL AS GroupName
FROM TEMPORARY.dbo.Users) K
WHERE UserName IS NOT NULL
GROUP BY UserName
ORDER BY SysAdmin DESC,
UserName ASC
-- Users exists in multiple groups
SELECT MemberName,
COUNT(*) AS Counter,
Util.dbo.StringConcat(GroupName, ', ') AS GroupList
FROM TEMPORARY.dbo.GroupMembers
GROUP BY MemberName
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC,
MemberName ASC
-- Windows logins but also exist in windows groups
SELECT *
FROM TEMPORARY.dbo.Users u
INNER JOIN TEMPORARY.dbo.GroupMembers g ON g.MemberName = u.UserName
ORDER BY u.UserName,
g.GroupName
SELECT *
FROM TEMPORARY.dbo.AllDatabasePrincipals
-- THIS IS RECURSIVE
SELECT *
FROM TEMPORARY.dbo.AllDatabaseUsers
--WHERE MemberName = 'RRUST'
SELECT *
FROM TEMPORARY.dbo.GroupMembers
-- These are not groups
SELECT *
FROM TEMPORARY.dbo.Users
-- Groups we could not query
SELECT *
FROM sys.server_principals p (NOLOCK)
WHERE p.TYPE = 'G'
AND p.NAME NOT LIKE 'NT SERVICE\%'
AND p.NAME NOT LIKE 'BUILTIN\%'
AND NOT EXISTS ( SELECT *
FROM TEMPORARY.dbo.GroupMembers g
WHERE g.GroupName = p.name AND MemberName IS NOT null)