USE [master]
GO
IF OBJECT_ID('AuditServerAndDatabasePermissions') IS NOT NULL
BEGIN
	DROP PROCEDURE AuditServerAndDatabasePermissions;
END
GO

/*************************************************************************************************
*** Server Permissions Audit ***
**************************************************************************************************

This stored procedure is used for auditing the permissions that exist on a SQL Server. The output 
contains five tables:

	1. Active Directory Groups in the SQL Server and the members of those groups
	2. Server Role assignments in the server
	3. Database Role permissions for each database
	4. Database Role assignments for each database
	5. User level object permissions outside of a role for each database

*/

CREATE PROCEDURE dbo.AuditServerAndDatabasePermissions
( @LoginToAudit NVARCHAR(128) = NULL )
AS
SET NOCOUNT ON
BEGIN

DECLARE @db NVARCHAR(128), 
		@SqlCmd NVARCHAR(MAX)


IF OBJECT_ID('tempdb..#GroupMembers') IS NOT NULL
	DROP TABLE #GroupMembers;

IF OBJECT_ID('tempdb..#LoginInfo') IS NOT NULL
	DROP TABLE #LoginInfo;
	
IF OBJECT_ID('tempdb..#ServerRoles') IS NOT NULL
	DROP TABLE #ServerRoles;

IF OBJECT_ID('tempdb..#DatabaseRoles') IS NOT NULL
	DROP TABLE #DatabaseRoles;
	
IF OBJECT_ID('tempdb..#DatabaseRoleAssignments') IS NOT NULL
	DROP TABLE #DatabaseRoleAssignments;
	
IF OBJECT_ID('tempdb..#UserLevelAssignments') IS NOT NULL
	DROP TABLE #UserLevelAssignments;
	
CREATE TABLE #GroupMembers 
(GroupName		NVARCHAR(128), 
 MemberList		NVARCHAR(MAX));
 
CREATE TABLE #LoginInfo
(account_name		VARCHAR(180),
 type				VARCHAR(180),
 privilege			VARCHAR(180),
 mapped_login_name	VARCHAR(180),
 permission_path	VARCHAR(180));
 
 CREATE TABLE #ServerRoles
(ServerRole		NVARCHAR(128), 
 MemberName		NVARCHAR(128))
 
CREATE TABLE #DatabaseRoles
(DatabaseName	NVARCHAR(128), 
 RoleName		NVARCHAR(128), 
 SchemaName		NVARCHAR(128),
 ObjectName		NVARCHAR(128), 
 PermissionType NVARCHAR(128), 
 StateDesc		NVARCHAR(128), 
 Grantor		NVARCHAR(128))
 
CREATE TABLE #DatabaseRoleAssignments
(DatabaseName	NVARCHAR(128), 
 RoleName		NVARCHAR(128), 
 UserName		NVARCHAR(128))
 
CREATE TABLE #UserLevelAssignments
(DatabaseName	NVARCHAR(128), 
 SchemaName		NVARCHAR(128), 
 ObjectName		NVARCHAR(128),
 ObjectType		NVARCHAR(128), 
 Grantee		NVARCHAR(128), 
 Grantor		NVARCHAR(128),
 UserType		NVARCHAR(128), 
 PermissionType NVARCHAR(128), 
 PermissionState NVARCHAR(128))


-- Generate the AD Group Member Lists
DECLARE @GroupName	NVARCHAR(128) = '';
DECLARE @MemberNames NVARCHAR(MAX) = '';

DECLARE group_cursor CURSOR FOR
SELECT name 
FROM sys.server_principals
WHERE type = N'G';

OPEN group_cursor

FETCH NEXT FROM group_cursor
INTO @GroupName

WHILE @@FETCH_STATUS = 0
BEGIN

SET @MemberNames = '';
TRUNCATE TABLE #LoginInfo

BEGIN TRY

INSERT INTO #LoginInfo (account_name, type, privilege, mapped_login_name, permission_path)
EXECUTE master..xp_logininfo @GroupName,'members';

SELECT @MemberNames = @MemberNames + 
	', '+account_name
FROM #LoginInfo

INSERT INTO #GroupMembers (GroupName, MemberList)
SELECT @GroupName, SUBSTRING(@MemberNames, 2, LEN(@MemberNames))
END TRY
BEGIN CATCH

INSERT INTO #GroupMembers (GroupName, MemberList)
SELECT @GroupName, SUBSTRING(@MemberNames, 2, LEN(@MemberNames))

END CATCH

FETCH NEXT FROM group_cursor
INTO @GroupName

END
CLOSE group_cursor
DEALLOCATE group_cursor


-- Step 1: Audit who is in server-level roles
INSERT INTO #ServerRoles(ServerRole, MemberName)
SELECT
	SUSER_NAME(rm.role_principal_id) AS ServerRole, 
	lgn.name AS MemberName
FROM sys.server_role_members rm
INNER JOIN sys.server_principals lgn
	ON rm.member_principal_id = lgn.principal_id
WHERE rm.role_principal_id >=3 
  AND rm.role_principal_id <=10;
  
-- Process Database Level Permissions with a cursor
DECLARE db_cursor CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR

	SELECT QUOTENAME(name)
    FROM master.sys.databases
	WHERE state_desc = N'ONLINE';

OPEN db_cursor;

FETCH NEXT FROM db_cursor
INTO @db;

   WHILE @@FETCH_STATUS = 0
   BEGIN

		-- Step 2: Audit roles on each database, defining what they are, 
		--		   what they can do, and who belongs in them
		INSERT INTO #DatabaseRoles 
			(DatabaseName, RoleName, SchemaName, ObjectName, 
			 PermissionType, StateDesc, Grantor)
		EXECUTE ('USE ' + @db + '; 
		SELECT 
			DB_NAME() AS DatabaseName, 
			dprin.name AS RoleName,
			ISNULL(sch.name, osch.name) AS SchemaName, 
			ISNULL(o.name, ''.'') AS ObjectName,
			dperm.permission_name, 
			dperm.state_desc, 
			grantor.name AS Grantor
		FROM sys.database_permissions AS dperm
		INNER JOIN sys.database_principals AS dprin
			ON dperm.grantee_principal_id = dprin.principal_id
		INNER JOIN sys.database_principals AS grantor
			ON dperm.grantor_principal_id = grantor.principal_id
		LEFT OUTER JOIN sys.schemas AS sch
			ON dperm.major_id = sch.schema_id 
				AND dperm.class = 3
		LEFT OUTER JOIN sys.all_objects AS o
			ON dperm.major_id = o.OBJECT_ID 
				AND dperm.class = 1
		LEFT OUTER JOIN sys.schemas AS osch
			ON o.schema_id = osch.schema_id
		WHERE dprin.name <> ''public'' 
		  AND dperm.type <> ''CO'' 
		  AND dprin.type = ''R''
			');

		-- Step 3: Audit the roles that users are in
		INSERT INTO #DatabaseRoleAssignments (DatabaseName, RoleName, UserName)
		EXECUTE ('USE ' + @db + '; 
		SELECT
			DB_NAME() AS DatabaseName,
			CASE 
				WHEN (r.principal_id IS NULL) THEN ''public''
				ELSE r.name
			END AS RoleName,
			u.name AS UserName
		FROM sys.database_principals AS u
		LEFT JOIN sys.database_role_members AS m 
			JOIN sys.database_principals AS r 
				ON m.role_principal_id = r.principal_id
			ON m.member_principal_id = u.principal_id
			');

		-- Step 4: Audit any users that have access to specific objects outside of a role
		INSERT INTO #UserLevelAssignments 
		EXECUTE ('USE ' + @db + '; 
		SELECT
			DB_NAME() AS DatabaseName, 
			ISNULL(sch.name, osch.name) AS SchemaName, 
			ISNULL(o.name, ''.'') AS ObjectName,
			o.type_desc,
			dprin.NAME AS Grantee,
			grantor.name AS Grantor,
			dprin.type_desc AS principal_type_desc,
			dperm.permission_name,
			dperm.state_desc AS permission_state_desc 
		FROM sys.database_permissions AS dperm
		INNER JOIN sys.database_principals AS dprin
			ON dperm.grantee_principal_id = dprin.principal_id
		INNER JOIN sys.database_principals AS grantor
			ON dperm.grantor_principal_id = grantor.principal_id
		LEFT OUTER JOIN sys.schemas AS sch
			ON dperm.major_id = sch.schema_id 
				AND dperm.class = 3
		LEFT OUTER JOIN sys.all_objects AS o
			ON dperm.major_id = o.OBJECT_ID 
			AND dperm.class = 1
		LEFT OUTER JOIN sys.schemas AS osch
			ON o.schema_id = osch.schema_id
		WHERE dprin.name <> ''public'' 
		  AND dperm.type <> ''CO'' 
		  AND dprin.type <> ''R''
			');

		FETCH NEXT FROM db_cursor 
		INTO @db;
	END
CLOSE db_cursor
DEALLOCATE db_cursor

IF @LoginToAudit IS NULL
BEGIN
	SELECT '-- Active Directory Group Membership'
	SELECT *
	FROM #GroupMembers
	ORDER BY 1;
	
	SELECT '-- Server Roles';
	SELECT *
	FROM #ServerRoles
	ORDER BY 1,2;

	SELECT '-- Database Roles' 
	SELECT * 
	FROM #DatabaseRoles 
	ORDER BY 1,2,3,4,5,6,7;

	SELECT '-- Database Role Assignments'
	SELECT * 
	FROM #DatabaseRoleAssignments 
	ORDER BY 1,2,3;
	
	SELECT '-- User Level Assignments'
	SELECT * 
	FROM #UserLevelAssignments 
	ORDER BY 1,2,3,4,5,6;
	
END 
ELSE BEGIN

	SELECT '-- Active Directory Group Membership'
	SELECT *
	FROM #GroupMembers
	ORDER BY 1;
	
	SELECT '-- Server Roles';
	SELECT *
	FROM #ServerRoles
	WHERE MemberName = @LoginToAudit
	ORDER BY 1,2;

	SELECT '-- Database Roles' 
	SELECT * 
	FROM #DatabaseRoles 
	ORDER BY 1,2,3,4,5,6,7;

	SELECT '-- Database Role Assignments'
	SELECT * 
	FROM #DatabaseRoleAssignments 
	WHERE UserName = @LoginToAudit
	ORDER BY 1,2,3;
	
	SELECT '-- User Level Assignments'
	SELECT * 
	FROM #UserLevelAssignments 
	WHERE Grantee = @LoginToAudit
	ORDER BY 1,2,3,4,5,6;

END

DROP TABLE #ServerRoles, #DatabaseRoles, #DatabaseRoleAssignments, #UserLevelAssignments

END

GO

EXECUTE dbo.AuditServerAndDatabasePermissions