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

Database object permissions

We could go down to more granular level and find out the access rights explicitly granted to each database user for each database object. This can be a quite large report, with probably not much value. But if you are still interested, you can execute a query like the following:

USE <<database_name>> — Execute for each database


SELECT a.name AS Database_Principal_Name,

       a.type_desc AS Database_Principal_Type,

       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 NOT IN (‘sys’, ‘INFORMATION_SCHEMA’, ‘public’, ‘guest’)   

ORDER BY a.name

Instead, you will probably be interested to find out the non-default schemas that exist in your database and the owners of those schemas:

USE <<database_name>> — Execute for each database


— List of non-standard schemas and their owners

SELECT a.name AS Database_Schema_Name, b.name AS Schema_Owner

FROM  sys.schemas a

       INNER JOIN sys.database_principals b

       ON a.principal_id = b.principal_id

WHERE a.schema_id <> a.principal_id

AND   b.type <> ‘R’

— List of users and their default schemas

SELECT name AS Database_User, Default_Schema_Name

FROM  sys.database_principals

WHERE type <> ‘R’

Certificates, symmetric and asymmetric keys

From version 2005, a new feature of data security has been added to SQL Server. Individual table columns can now be encrypted using asymmetric keys, symmetric keys or certificates. Data traffic between SQL Servers can be encrypted using certificates and login accounts can be mapped to certificates or asymmetric keys.

When it comes to encryption, there is a hierarchical relationship between these three entities. A data field can be encrypted using a symmetric key, which in turn can be encrypted by an asymmetric key or a certificate. Certificates can also encrypt asymmetric keys. At the root of all encryption mechanism is the service master key.

When using certificates, SQL Server does not necessarily need a certificate issued by a third party like VeriSign. In fact it can issue a self signed certificate itself.

If you want to see if there are asymmetric keys, symmetric keys of certificates installed in your database, you can either use the Management Studio or use a query like the following:

USE <<ddatabase_name>> — Change for each database


SELECT * FROM sys.certificates

SELECT * FROM sys.symmetric_keys

SELECT * FROM sys.asymmetric_keys


Leave a comment

Your email address will not be published.