Security Change Snapshot

SET NOCOUNT ON;

DECLARE @Login NVARCHAR(128)
		, @ModDate DATETIME
		, @Database NVARCHAR(128)
		, @SQL NVARCHAR(MAX);

SET @Login = NULL;
SET @ModDate = '2/1/2013';

----- SERVER LOGINS -----
IF @Database IS NULL
	SELECT	[ServerLogin] = sp.name
			, [LoginType] = sp.type_desc
			, [ServerRole] = STUFF((
								SELECT	',' + sp2.name
								FROM	master.sys.server_role_members rm
										INNER JOIN master.sys.server_principals sp2 ON rm.role_principal_id = sp2.principal_id
								WHERE	sp.principal_id = rm.member_principal_id
								FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'')
			, [DefaultDB] = sp.default_database_name
			, [LastModified] = sp.modify_date
	FROM	master.sys.server_principals sp
	WHERE	sp.is_disabled = 0
	AND		sp.name = ISNULL(@Login, sp.name)
	AND		sp.modify_date >= ISNULL(@ModDate,sp.modify_date)
	ORDER BY sp.name;

----- DATABASE PERMISSIONS -----
CREATE TABLE #DB (
	[Database] NVARCHAR(128)
	, [PermissionState] NVARCHAR(60)
	, [Permission] NVARCHAR(128)
	, [Object] NVARCHAR(128)
	, [Login] NVARCHAR(128)
	, LoginType NVARCHAR(60)
	, [LastModified] DATETIME
);

SET @SQL = 'USE ?;
INSERT INTO #DB (
	[Database]
	, [PermissionState]
	, [Permission]
	, [Object]
	, [Login]
	, LoginType
	, [LastModified]
)
SELECT	[Database] = DB_NAME()
		, [PermissionState] = p.state_desc
		, [Permission] = p.permission_name
		, [Object] = CASE WHEN p.class = 0 THEN ''DB: '' + DB_NAME(p.major_id)
				WHEN p.class = 3 THEN ''Schema: '' + s.name
				ELSE ''Object: '' + OBJECT_NAME(p.major_id)
			END
		, [Login] = dp.name
		, LoginType = dp.type_desc
		, [LastModified] = dp.modify_date
FROM	sys.database_principals dp
		INNER JOIN sys.database_permissions p ON dp.principal_id = p.grantee_principal_id
		LEFT OUTER JOIN sys.objects so ON p.major_id = so.object_id AND p.class = 1
		LEFT OUTER JOIN sys.schemas s ON p.major_id = s.schema_id AND p.class = 3
WHERE	dp.name = ISNULL(' + CASE WHEN @Login IS NOT NULL THEN '''' + @Login + '''' ELSE 'NULL' END + ', dp.name)
AND		dp.modify_date >= ISNULL(' + CASE WHEN @ModDate IS NOT NULL THEN '''' + CONVERT(VARCHAR,@ModDate) + '''' ELSE 'NULL' END + ',dp.modify_date);';

EXECUTE sp_MSforeachdb @SQL;

SELECT	[Database]
		, [PermissionState]
		, [Permission]
		, [Object]
		, [Login]
		, LoginType
		, [LastModified]
FROM	#DB
WHERE	[Database] = ISNULL(@Database, [Database])
ORDER BY [Database]
		, [Login]
		, [Object];

----- ROLE MEMBERS -----
CREATE TABLE #RM (
	[Database] NVARCHAR(128)
	, [Role] NVARCHAR(128)
	, [Login] NVARCHAR(512)
	, [LastModified] DATETIME
);

SET @SQL = 'USE ?;
SET QUOTED_IDENTIFIER ON;
INSERT INTO #RM (
	[Database]
	, [Role]
	, [Login]
	, [LastModified]
)
SELECT	[Database] = DB_NAME()
		, [Role]
		, [Login]
		, [LastModified]
FROM	(
			SELECT	[Role] = sp.name
					, [Login] = STUFF((
								SELECT	'','' + sp2.name
								FROM	sys.database_role_members rm
										INNER JOIN sys.database_principals sp2 ON rm.member_principal_id = sp2.principal_id
								WHERE	rm.role_principal_id = sp.principal_id
								AND		sp2.name = ISNULL(' + CASE WHEN @Login IS NOT NULL THEN '''' + @Login + '''' ELSE 'NULL' END + ', sp2.name)
								AND		sp2.modify_date >= ISNULL(' + CASE WHEN @ModDate IS NOT NULL THEN '''' + CONVERT(VARCHAR,@ModDate) + '''' ELSE 'NULL' END + ', sp2.modify_date)
								FOR XML PATH(''''),TYPE).value(''.'',''VARCHAR(MAX)''),1,1,'''')
					, [LastModified] = sp.modify_date
			FROM	sys.database_principals sp
			WHERE	sp.type IN (''R'',''A'') --DATABASE_ROLE,APPLICATION_ROLE
		) x
WHERE	x.[Login] IS NOT NULL;';

EXECUTE sp_MSforeachdb @SQL;

SELECT	[Database]
	, [Role]
	, [Login]
	, [LastModified]
FROM	#RM
WHERE	[Database] = ISNULL(@Database, [Database])
ORDER BY [Database]
		, [Role]
		, [Login];

DROP TABLE #DB;
DROP TABLE #RM;