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:
Instead, you will probably be interested to find out the non-default schemas that exist in your database and the owners of those schemas:
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: