In Part 1 of this series, I presented a T-SQL query that will return a summary of login permissions within a SQL 2005/2008 database. I also mentioned that SQL 2000 requires a slightly different query.

Here is that query.

In the upcoming Part 3, I will start diving into the Powershell script that drives this process – stay tuned!

SELECT
   ServerName              = @@SERVERNAME,
   LoginName               = AccessSummary.LoginName,
   LoginType               = CASE WHEN syslogins.isntuser = 1 THEN 'WINDOWS_LOGIN' WHEN syslogins.isntgroup = 1 THEN 'WINDOWS_GROUP' ELSE 'SQL_USER' END,
   DatabaseName            = DB_NAME(),
   SelectAccess            = MAX(AccessSummary.SelectAccess),
   InsertAccess            = MAX(AccessSummary.InsertAccess),
   UpdateAccess            = MAX(AccessSummary.UpdateAccess),
   DeleteAccess            = MAX(AccessSummary.DeleteAccess),
   DBOAccess               = MAX(AccessSummary.DBOAccess),
   SysadminAccess          = MAX(AccessSummary.SysadminAccess)
FROM
   (
       /* Get logins with permissions */       SELECT 
           LoginName               = sysusers.name,
           SelectAccess            = CASE WHEN sysprotects.action = 193 THEN 1 ELSE 0 END,
           InsertAccess            = CASE WHEN sysprotects.action = 195 THEN 1 ELSE 0 END,
           UpdateAccess            = CASE WHEN sysprotects.action = 196 THEN 1 ELSE 0 END,
           DeleteAccess            = CASE WHEN sysprotects.action = 197 THEN 1 ELSE 0 END,
           DBOAccess               = 0,
           SysadminAccess          = 0
       FROM dbo.sysobjects
       INNER JOIN dbo.sysprotects
           ON sysprotects.id = sysobjects.id
       INNER JOIN dbo.sysusers
           ON sysusers.uid = sysprotects.uid
       INNER JOIN MASTER.dbo.syslogins AS syslogins
           ON syslogins.sid = sysusers.sid
       WHERE sysobjects.TYPE IN ('U', 'V')
           AND sysusers.issqlrole = 0
           AND sysprotects.protecttype IN (204, 205)
           AND sysprotects.action IN (193, 195, 196, 197)
       UNION ALL
       /* Get group members with permissions */       SELECT 
           LoginName               = sysusersMember.name,
           SelectAccess            = CASE WHEN sysprotects.action = 193 THEN 1 ELSE 0 END,
           InsertAccess            = CASE WHEN sysprotects.action = 195 THEN 1 ELSE 0 END,
           UpdateAccess            = CASE WHEN sysprotects.action = 196 THEN 1 ELSE 0 END,
           DeleteAccess            = CASE WHEN sysprotects.action = 197 THEN 1 ELSE 0 END,
           DBOAccess               = 0,
           SysadminAccess          = 0
       FROM dbo.sysobjects
       INNER JOIN dbo.sysprotects
           ON sysprotects.id = sysobjects.id
       INNER JOIN dbo.sysusers AS sysusersRole
           ON sysusersRole.uid = sysprotects.uid
       INNER JOIN dbo.sysmembers
           ON sysmembers.groupuid = sysusersRole.uid
       INNER JOIN dbo.sysusers AS sysusersMember
           ON sysusersMember.uid = sysmembers.memberuid
       INNER JOIN MASTER.dbo.syslogins AS syslogins
           ON syslogins.sid = sysusersMember.sid
       WHERE sysobjects.TYPE IN ('U', 'V')
           AND sysusersRole.issqlrole = 1
           AND sysusersRole.name NOT IN ('public')
           AND sysprotects.protecttype IN (204, 205)
           AND sysprotects.action IN (193, 195, 196, 197)
       UNION ALL
       /* Get users in db_owner, db_datareader and db_datawriter */       SELECT
           LoginName               = syslogins.name,
           SelectAccess            = CASE WHEN sysusers.name IN ('db_owner', 'db_datareader') THEN 1 ELSE 0 END,
           InsertAccess            = CASE WHEN sysusers.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,
           UpdateAccess            = CASE WHEN sysusers.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,
           DeleteAccess            = CASE WHEN sysusers.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,
           DBOAccess               = CASE WHEN sysusers.name IN ('db_owner') THEN 1 ELSE 0 END,
           SysadminAccess          = 0
       FROM dbo.sysusers
       INNER JOIN dbo.sysmembers
           ON sysmembers.groupuid = sysusers.uid
       INNER JOIN dbo.sysusers AS sysusersMember
           ON sysusersMember.uid = sysmembers.memberuid
       INNER JOIN MASTER.dbo.syslogins AS syslogins
           ON syslogins.sid = sysusersMember.sid
       WHERE sysusers.name IN ('db_owner', 'db_datareader', 'db_datawriter')
       UNION ALL
       /* Get users in sysadmin */       SELECT
           LoginName               = syslogins.name,
           SelectAccess            = 1,
           InsertAccess            = 1,
           UpdateAccess            = 1,
           DeleteAccess            = 1,
           DBOAccess               = 0,
           SysadminAccess          = 1
       FROM MASTER.dbo.syslogins AS syslogins
       WHERE syslogins.sysadmin = 1
   ) AS AccessSummary
INNER JOIN MASTER.dbo.syslogins AS syslogins
   ON syslogins.loginname = AccessSummary.LoginName
WHERE AccessSummary.LoginName NOT IN ('NT SERVICE\MSSQLSERVER', 'NT AUTHORITY\SYSTEM', 'NT SERVICE\\SQLSERVERAGENT')
GROUP BY
   AccessSummary.LoginName,
   CASE WHEN syslogins.isntuser = 1 THEN 'WINDOWS_LOGIN' WHEN syslogins.isntgroup = 1 THEN 'WINDOWS_GROUP' ELSE 'SQL_USER' END

The post Automated Permissions Auditing With Powershell and T-SQL: Part 2 appeared first on RealSQLGuy.