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.
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:
If you want to find out the login that is mapped to the built-in “dbo” user, you can use the following query:
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.
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.