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 1

rubbergloveshouse

A big part of my job is controlling security to multiple production databases, and reporting on that security for quarterly audits.  In my environment, it’s not important to know who has access to specific tables or other objects, but we are interested in knowing who has some form of read or write access to a database, or who might be a DBO or sysadmin.

This was largely a manual process, until I decided to dive into Powershell.  Once I began to realize the power of Powershell (pun intended), I began to scheme ways that it could help provide better visibility into our database security.

After a few iterations, I finally settled on a combination of Powershell and T-SQL.  It’s not terribly complicated, but the overall script is quite large, so I’ll present it here in functional parts, and make the overall script available.

Without further ado, let’s begin…

The first functional piece of the audit script that I’d like to present is the T-SQL query used to collect login details from my SQL 2005 and SQL 2008 servers.  A different query is used for SQL 2000, which I’ll present later.  The Powershell script determines the version of a particular SQL Server instance and decides which query to run – I’ll present this part later as well.

This query returns every login from a SQL 2005/2008 database, including bits to indicate that the login has read access, write access, DBO access, or sysadmin access.

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           = sysDatabasePrincipal.name,
           SelectAccess        = CASE WHEN permission_name = 'SELECT' THEN 1 ELSE 0 END,
           InsertAccess        = CASE WHEN permission_name = 'INSERT' THEN 1 ELSE 0 END,
           UpdateAccess        = CASE WHEN permission_name = 'UPDATE' THEN 1 ELSE 0 END,
           DeleteAccess        = CASE WHEN permission_name = 'DELETE' THEN 1 ELSE 0 END,
           DBOAccess           = 0,
           SysadminAccess      = 0
       FROM sys.database_permissions AS sysDatabasePermission
       INNER JOIN sys.database_principals AS sysDatabasePrincipal
           ON sysDatabasePrincipal.principal_id = sysDatabasePermission.grantee_principal_id
       INNER JOIN sys.server_principals AS sysServerPrincipal
           ON sysServerPrincipal.sid = sysDatabasePrincipal.sid
       WHERE sysDatabasePermission.class_desc = 'OBJECT_OR_COLUMN'
           AND sysDatabasePrincipal.type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP', 'SQL_USER')
           AND sysServerPrincipal.is_disabled = 0
       UNION ALL
       /* Get group members with permissions */
       SELECT 
           LoginName           = sysDatabasePrincipalMember.name,
           SelectAccess        = CASE WHEN permission_name = 'SELECT' THEN 1 ELSE 0 END,
           InsertAccess        = CASE WHEN permission_name = 'INSERT' THEN 1 ELSE 0 END,
           UpdateAccess        = CASE WHEN permission_name = 'UPDATE' THEN 1 ELSE 0 END,
           DeleteAccess        = CASE WHEN permission_name = 'DELETE' THEN 1 ELSE 0 END,
           DBOAccess           = 0,
           SysadminAccess      = 0
       FROM sys.database_permissions AS sysDatabasePermission
       INNER JOIN sys.database_principals AS sysDatabasePrincipalRole
           ON sysDatabasePrincipalRole.principal_id = sysDatabasePermission.grantee_principal_id
       INNER JOIN sys.database_role_members AS sysDatabaseRoleMember
           ON sysDatabaseRoleMember.role_principal_id = sysDatabasePrincipalRole.principal_id
       INNER JOIN sys.database_principals AS sysDatabasePrincipalMember
           ON sysDatabasePrincipalMember.principal_id = sysDatabaseRoleMember.member_principal_id
       INNER JOIN sys.server_principals AS sysServerPrincipal
           ON sysServerPrincipal.sid = sysDatabasePrincipalMember.sid
       WHERE sysDatabasePermission.class_desc = 'OBJECT_OR_COLUMN'
           AND sysDatabasePrincipalRole.type_desc = 'DATABASE_ROLE'
           AND sysDatabasePrincipalRole.name <> 'public'
           AND sysDatabasePrincipalMember.type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP', 'SQL_USER')
           AND sysServerPrincipal.is_disabled = 0
       UNION ALL
       /* Get users in db_owner, db_datareader and db_datawriter */
       SELECT
           LoginName           = sysServerPrincipal.name,
           SelectAccess        = CASE WHEN sysDatabasePrincipalRole.name IN ('db_owner', 'db_datareader') THEN 1 ELSE 0 END,
           InsertAccess        = CASE WHEN sysDatabasePrincipalRole.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,
           UpdateAccess        = CASE WHEN sysDatabasePrincipalRole.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,
           DeleteAccess        = CASE WHEN sysDatabasePrincipalRole.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,
           DBOAccess           = CASE WHEN sysDatabasePrincipalRole.name = 'db_owner' THEN 1 ELSE 0 END,
           SysadminAccess      = 0
       FROM sys.database_principals AS sysDatabasePrincipalRole
       INNER JOIN sys.database_role_members AS sysDatabaseRoleMember
           ON sysDatabaseRoleMember.role_principal_id = sysDatabasePrincipalRole.principal_id
       INNER JOIN sys.database_principals AS sysDatabasePrincipalMember
           ON sysDatabasePrincipalMember.principal_id = sysDatabaseRoleMember.member_principal_id
       INNER JOIN sys.server_principals AS sysServerPrincipal
           ON sysServerPrincipal.sid = sysDatabasePrincipalMember.sid
       WHERE sysDatabasePrincipalRole.name IN ('db_owner', 'db_datareader', 'db_datawriter')
           AND sysServerPrincipal.type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP', 'SQL_LOGIN')
           AND sysServerPrincipal.is_disabled = 0
       UNION ALL
       /* Get users in sysadmin */
       SELECT
           LoginName           = sysServerPrincipalMember.name,
           SelectAccess        = 1,
           InsertAccess        = 1,
           UpdateAccess        = 1,
           DeleteAccess        = 1,
           DBOAccess           = 0,
           SysadminAccess      = 1
       FROM sys.server_principals AS sysServerPrincipalRole
       INNER JOIN sys.server_role_members AS sysServerRoleMember
           ON sysServerRoleMember.role_principal_id = sysServerPrincipalRole.principal_id
       INNER JOIN sys.server_principals AS sysServerPrincipalMember
           ON sysServerPrincipalMember.principal_id = sysServerRoleMember.member_principal_id
       WHERE sysServerPrincipalMember.type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP', 'SQL_LOGIN')
           AND sysServerPrincipalMember.is_disabled = 0
   ) 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 1 appeared first on RealSQLGuy.

Comments

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

Loading comments...