SQL Server Security Audit (Part 2) – Database Level Audit

Once the Server Level audits described in Part 1 of the Security Audit series are complete, databases should be considered next. A number of steps can be taken to audit database level security.

Database owners

A database owner can perform any action in the database. This includes granting access rights to the database to other users. Every database has a built-in user account called the “dbo”. This is the database owner. By default, this user account is mapped to the login that created the database. There is also a fixed database role called “db_owner” whose members have database ownership privilege.

Just like finding out who has system administrator privilege in your SQL Server, you may want to find out who has ownership rights in each database.

Executing the following script against each database will give you a list of user accounts that are members of the db_owner role:

USE <<database_Name>> — Run against each database


SELECT c.name AS DB_Owner_Role_Member

FROM  sys.database_principals a

       INNER JOIN sys.database_role_members b

        ON a.principal_id = b.role_principal_id AND a.is_fixed_role = 1 AND a.name =’db_owner’

            INNER JOIN sys.database_principals c

            ON b.member_principal_id = c.principal_id

If you want to find out the login that is mapped to the built-in “dbo” user, you can use the following query:

USE <<database_name>> — Execute for each database


SELECT b.name AS Login_Mapped_to_DBO

FROM  sys.database_principals a

       INNER JOIN sys.server_principals b

       ON a.sid = b.sid

WHERE a.name = ‘dbo’

Guest user account

Just like dbo, the “guest” account is also a special built-in database user. This user is disabled by default, but you can enable it or create it manually. The guest account does not correspond to any SQL Server login. In fact its purpose is to serve as an ad-hoc user for any login. What this means is that any login that does not have a corresponding database user account can still get into the database provided the database has the guest user enabled. Guest user exists in the system databases – and they are there for a reason. But if you have a guest account enabled in your production database, you need to know why it is there and what access it has got.

A code snippet like the following can help you identify the rights of the guest user.

USE <<database_name>> — Execute for each database


SELECT   c.name AS ObjectName,

         c.type_desc AS Object_Type,

         b.permission_name AS Permission_Type,

         b.state_desc AS Permission_Status

FROM     sys.database_principals    a

         INNER JOIN sys.database_permissions    b    

         ON a.principal_id = b.grantee_principal_id

            INNER JOIN sys.objects c

            ON b.major_id = c.object_id        

WHERE    a.name = ‘guest’

ORDER BY c.name

Orphan users

Strictly speaking, this is not a security hole, but one that needs to be looked at nevertheless. Sometimes SQL Server logins are deleted without dropping the associated database users first. Often databases are restored from another system and the user accounts in them do not correspond to any local logins. Your database is said to have “orphan” users in such cases. The security audit should pick up these orphan users in each database. The following query can be run in each database for this purpose.

USE <<database_name>> — Execute for each database


SELECT      a.name AS OrphanUserName, a.type_desc AS UserType

FROM  sys.database_principals a

      LEFT OUTER JOIN   sys.server_principals   b

      ON    a.sid = b.sid


AND         a.type In (‘S’, ‘U’, ‘G’)

AND         a.name NOT in (‘sys’, ‘INFORMATION_SCHEMA’, ‘guest’)



Leave a comment

Your email address will not be published.