Audit Scripts

scripts required to pull the users tied up with the roles , AD groups , Group Members and their roles.

 

Query-1 — Adgroups , Group members , Roles assigned.

SET NOCOUNT ON
DECLARE @PERMISSION TABLE(
DATABASE_NAME SYSNAME,
USER_ROLE_NAME SYSNAME,
ACCOUNT_TYPE NVARCHAR(60
),
ACTION_TYPE NVARCHAR(128),
PERMISSION NVARCHAR(60),
OBJECTNAME SYSNAME NULL,
OBJECT_TYPE NVARCHAR(60),
HAS_DB_ACCESS BIT
)
DECLARE @DBS TABLE(DBNAME SYSNAME)
DECLARE @NEXT SYSNAME
INSERT INTO @DBS
SELECT NAME FROM SYS.DATABASES WHERE NAME NOT IN (‘TEST_DB’) — DATBASES WANT TO EXCLUDE FROM AUDIT HERE TEST_DB IS EXAMPLE
ORDER BY NAME
SELECT TOP 1 @NEXT =DBNAME FROM @DBS
WHILE (@@ROWCOUNT<>0)
BEGIN
INSERT INTO @PERMISSION
EXEC (‘USE [‘+ @NEXT + ‘]
DCLARE @OBJECTS TABLE (OBJ_ID INT,OBJ_TYPE CHAR(2))
INSERT INTO @OBJECTS
SELECT ID,XTYPE FROM MASTER.SYS.SYSOBJECTS
INSERT INTO @OBJECTS
SELECT OBJECT_ID,TYPE FROM SYS.OBJECTS
SELECT ”’+@NEXT +”’, A.NAME AS ”USER OR ROLE NAME”,A.TYPE_DESC AS ”ACCOUNT TYPE”,
D.PERMISSION_NAME AS “TYPE OF PERMISSION” , D.STATE_DESC AS “STATE OF PERMISSION”,
OBJECT_SCHEMA_NAME”(D.MAJOR_ID)”+”.”+OBJECT_NAME”(D.MAJOR_ID)” AS “OBJECT_NAME”, CASE E.OBJECT_TYPE
WHEN “AF” THEN “AGGREGATE FUNCTION (CLR)”
WHEN “C” THEN “CHECK CONSTRAINT”
WHEN “D” THEN “DEFAULT (CONSTRAINT OR STAND_ALONE)”
WHEN “F” THEN “FOREIGNKEY CONSTRAINT”
WHEN “PK” THEN “PRIMARYKEY CONSTRAINT”
WHEN “P” THEN “SQL STORED PROCEDURE”
WHEN “PC” THEN “ASSEMBLY (CLR) SCALAR FUNCTION”
WHEN “FN” THEN “SQL SCALAR FUNCTION”
WHEN “FS” THEN “ASSEMBLY (CLR) SCALAR FUNCTION”
WHEN “FT” THEN “ASSEMBLY (CLR) TABLE_VALUED FUNCTION”
WHEN “R” THEN “RULE (OLD_STYLE,STAND-ALONE)”
WHEN “RF” THEN “REPLCATION-FILTER-PROCEDURE”
WHEN “S” THEN “SYATEM BASE TABLE”
WHEN “SN” THEN “SYNONYUM”
WHEN “SQ” THEN ” SERVICE QUEUE”
WHEN “TA” THEN “ASSEMBLY (CLR) DML TRIGGER”
WHEN “TR” THEN “SQL DML TRIGGER”
WHEN “TF” THEN “SQL INLINE TABLE-VALUED FUNCTION”
WHEN “U” THEN “TABLE(USER-DEFINED)”
WHEN “UQ” THEN “UNIQUE CONSTRAINT”
WHEN “V” THEN “VIEW”
WHEN “X” THEN “EXTENDED STORED PROCEDURE”
WHEN “IT” THEN “INTERNAL TABLE”
ENDAS “OBJECT TYPE”,SU.HASDBACCESS AS “HAS_DB_ACCESS”
FROM [‘ +@NEXT+ ‘].SYS.DATABSE_PRINCIPALS A
LEFT JOIN [‘ +@NEXT+ ‘].SYS.DATABASE_PERMISSIONS D ON A.PRINCIPLE_ID=D.GRANTEE_PRINCIPAL_ID
LEFT JOIN @OBJECTS E ON D.MAJOR_ID=E.OBJ_ID
LEFT OUTER JOIN SYS.SYSUSERS SU ON A.PRINCIPAL_ID=SU.UID
ORDER BY A.NAME,D.CLASS_DESC ”)
DELETE @DBS WHERE DBNAME=@NEXT
SELECT TOP 1 @NEXT=DBNAME FROM @DBS
END
SET NOCOUNT OFF
SELECT DISTINCT @@SERVERNAME AS SERVER_NAME,DATABASE_NAME,USER_ROLE_NAME,ACCOUNT_TYPE,ACTION_TYPE,PERMISSION,OBJECTNAME,OBJECT_TYPE,HAS_DB_ACCESS
FROM @PERMISSION

Script -2

DB roles and Members

— FIND DBROLES AND MEMEBERS TAGED TO ROLES
BEGIN
DECLARE @TMP TABLE(HOST VARCHAR(255),DBA_NAME VARCHAR(255),DATABASE_ROLE VARCHAR(255),WINDOWSADGROUP_SID_FID VARCHAR(255))
DECLARE @COMMAND VARCHAR(1000)
SELECT @COMMAND =’USE ?; WITH ROLEMEMBERS (MEMBER_PRINCIPAL_ID,ROLE_PRINCIPAL_ID)
AS
( SELECT RM1.MEMBER_PRINCIPAL_ID,
RM1.ROLE_PRINCIPAL_ID FROM SYS.DATABASE_ROLE_MEMBERS RM1(NOLOCK)
UNION ALL
SELECT D.MEMBER_PRINCIPAL_ID,RM.ROLE_PRINCIPAL_ID FROM SYS.DATABASE_ROLE_MEMBERS RM(NO LOCK)
INNER JOIN ROLEMEMBERS AS D
ON RM.MEMBER_PRINCIPAL_ID=D.ROLE_PRINCIPAL_ID)
SELECT DISTINCT @@SERVERNAME AS HOST,DB_NAME() AS DBNAME,RP.NAME AS DATABASE_ROLE,MP.NAME AS WINDOWSADGROUP_SID_FID
FROM ROLEMEMBERS DRM
JOIN SYS.DATABASE_PRINCIPALS RP ON (DRM.ROLE_PRINCIPAL_ID=RP.PRINCIPAL_ID)
JOIN SYS.DATABASE_PRINCIPALS MP ON (DRM.MEMBER_PRINCIPAL_ID=MP.PRINCIPAL_ID)
ORDER BY WINDOWSADGROUP_SID_FID,DATABASE_ROLE’
INSERT INTO @TMP
EXEC SP_MSFOREACHDB @COMMAND
SELECT * FROM @TMP
END
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s