Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

RealSQLGuy - Helping You To Become A SQL Hero

My real name is Tracy McKibben. I’ve been working with database products for over 20 years, starting with FoxBase running on Xenix. Over the years, I’ve worked with all flavors of FoxPro, some Clipper and dBase, and starting somewhere around 1995, SQL Server. I’ve even dabbled with Oracle, though I’ve tried to block out all memories of that experience. At present, I’m the Senior SQL Server DBA and the DBA Team Supervisor for Pearson VUE. All opinions expressed on this site are my own and do not reflect the opinions of Pearson VUE.

Automated Permissions Auditing With Powershell and T-SQL: Part 2

rubbergloveshouse

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.

Comments

Leave a comment on the original post [realsqlguy.com, opens in a new window]

Loading comments...