How to check who has access to symmetric keys in SQL Server

Question

I would like to know all the users that have access to symmetric keys and the type of access they have. Can you please let me know how I can do this?

score 6 · Answer 1 · edited Oct 15 '15 at 19:39

The answers above do not actually reflect the Object Name, please consider this instead:

select
      [database] = db_name()
    , u.name
    , p.permission_name
    , p.class
    , p.class_desc
    , ObjectNameForObjectORColumn
        = object_name(p.major_id) 
    , objectNameActual
        = case class_desc
            when 'SYMMETRIC_KEYS' then sm.name              
            when 'CERTIFICATE' then [cert].name             
      end
    , state_desc 
from sys.database_permissions  p 
inner join sys.database_principals u
    on p.grantee_principal_id = u.principal_id
left outer join sys.symmetric_keys sm
    on p.major_id = sm.symmetric_key_id
    and p.class_desc = 'SYMMETRIC_KEYS'
left outer join sys.certificates [cert]
    on p.major_id = [cert].[certificate_id]
    and p.class_desc = 'CERTIFICATE'
where class_desc in('SYMMETRIC_KEYS', 'CERTIFICATE')
order by u.name

More here https://danieladeniji.wordpress.com/2015/10/09/sql-server-list-permissions-for-user/

ObjectNameForObjectORColumn vs objectNameActual is fabulous – JJS Dec 07 '18 at 20:24 — JJS, Dec 07 '18 at 20:24

score 4 · Accepted Answer · answered Jun 13 '14 at 18:40

4

Maybe this query can help:

select u.name, p.permission_name, p.class_desc, 
    object_name(p.major_id) ObjectName, state_desc 
from sys.database_permissions  p join sys.database_principals u
on p.grantee_principal_id = u.principal_id
where class_desc = 'SYMMETRIC_KEYS'

answered Jun 13 '14 at 18:40

Dusan

791
5
16

This query helps. Thank you very much. – user3229801 Jun 13 '14 at 19:17
Found this resource helpful: https://mssqlguru.wordpress.com/2009/05/05/protection-of-dbms-asymmetric-encryption-keys/. It further breaks down the logic of a related database vulnerability scan finding. – Draghon Sep 25 '15 at 19:52

score 0 · Answer 3 · edited Oct 09 '15 at 21:56

This improved query provides the name of the current database, to avoid mistakes. Additionally, if you're using SYMMETRIC KEYS, usually you're also using CERTIFICATES, that's why is a good choice to view the CERTIFICATE permissions as well:

select db_name(), u.name, p.permission_name, p.class_desc, 
    object_name(p.major_id) ObjectName, state_desc 
from sys.database_permissions  p join sys.database_principals u
    on p.grantee_principal_id = u.principal_id
    where class_desc in('SYMMETRIC_KEYS', 'CERTIFICATE')
order by u.name

How to check who has access to symmetric keys in SQL Server

3 Answers3