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
GO
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
GO
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
GO
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
GO
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
WHERE b.sid IS NULL
AND a.type In (‘S’, ‘U’, ‘G’)
AND a.name NOT in (‘sys’, ‘INFORMATION_SCHEMA’, ‘guest’)