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
GO
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
GO
— 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
GO
SELECT * FROM sys.certificates
SELECT * FROM sys.symmetric_keys
SELECT * FROM sys.asymmetric_keys
]]>