locked
List/show users that have access to symmetric keys SQL 2005 RRS feed

  • Question

  • A sqluser recently lost permission to a symmetric key.  I only knew this by re-running the 'grant control on symmetric key...'  statement.  The appropriate code than ran correctly.   That made me want to find a way to see the problem before blindly fixing it...

    I want to be able to see what users have access (permissions [and which permissions]) to symmetric keys and certificates.  Where do i go to see that in sql 2005?


    steve
    Wednesday, April 7, 2010 6:09 PM

Answers

  • You should be able to run this query to see the information that you are after:

    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'
    order by ObjectName, name, p.permission_name

    For what it is worth, I personally prefer to grant this kind of permission to a role, then put the necessary users in the role.  If you do that, then you can check role memberships by:

    select u.name ServerRole, u2.name Member 
    from sys.Server_role_members m
    join sys.Server_principals u on m.role_principal_id = u.principal_id
    join sys.Server_principals u2 on m.member_principal_id = u2.principal_id
    where u.name = 'SymetricKeyRole'
    order by ServerRole

    RLF

    • Proposed as answer by Lekss Thursday, April 8, 2010 12:03 AM
    • Edited by SQLWork Thursday, April 8, 2010 12:26 PM Corrected spelling per steve.schneider
    • Marked as answer by Tom Li - MSFT Friday, April 9, 2010 7:23 AM
    Wednesday, April 7, 2010 7:38 PM

All replies

  • You should be able to run this query to see the information that you are after:

    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'
    order by ObjectName, name, p.permission_name

    For what it is worth, I personally prefer to grant this kind of permission to a role, then put the necessary users in the role.  If you do that, then you can check role memberships by:

    select u.name ServerRole, u2.name Member 
    from sys.Server_role_members m
    join sys.Server_principals u on m.role_principal_id = u.principal_id
    join sys.Server_principals u2 on m.member_principal_id = u2.principal_id
    where u.name = 'SymetricKeyRole'
    order by ServerRole

    RLF

    • Proposed as answer by Lekss Thursday, April 8, 2010 12:03 AM
    • Edited by SQLWork Thursday, April 8, 2010 12:26 PM Corrected spelling per steve.schneider
    • Marked as answer by Tom Li - MSFT Friday, April 9, 2010 7:23 AM
    Wednesday, April 7, 2010 7:38 PM
  • Thank you Russell,  That is exactly what i needed.  (Caveat... add an "S" ... where class_desc = 'symmetric_keyS'


    steve
    Thursday, April 8, 2010 11:37 AM