On SQL 2005 and above, how can I find users and database level permissions mapped to a certificate?

Story behing this: I copied a database from SQL 2005 to SQL 2008. It had a certificate inside the database which got copied over with backup and restore (as far as I can see). I am not seeing any server level credentials for this certificate on both source and destination server and can see this inside the user database using the following code:

select * from sys.certificates

I couldn't find a certificate file on the server corresponding to this certificate. I doubt the following with this:

  • Did the certificate get copied correctly?
  • Did the logins get mapped automatically?
  • Did it loose any of the permissions on the database objects?
  • How can I find a file corresponding to this certificate?

I just want to make sure that If the logins were granted permissions using a certificate, then I should be able to see and retain the permissions. The database doesn't have encrypted data.

Thanks in advance..


You can use this table to find users mapped to certificate

select * from sys.database_principals where type = 'C'

If the principals are SQL users, maybe you will have to remap users because when you migrate users and logins from one sql server to an other, SID aren't regenerated and may be different on the new server. So all the link on the users aren't valid.

Hope this help you.

  • Thanks.. but when I run it, I see no user mapping in the existing environment and the owning_principal_id = NULL. Does it mean that it is not mapped to anything or the principal who created it no longer exists?
    – Manjot
    Apr 17 '11 at 22:25
  • 1
    Have you migrate all the logins/users from SQL 2005 to SQL 2008? You can use this link to find out how to transfer support.microsoft.com/kb/918992
    – Nico
    Apr 17 '11 at 22:45
  • Yes I have but thats the thing... certificate credential isnt showing up on the server level. It is only showing in databases.
    – Manjot
    Apr 19 '11 at 22:58
  • Have you try to remap the user to the certificate ? You can issue a GRANT to remap the user msdn.microsoft.com/en-us/library/ms186278.aspx
    – Nico
    Apr 19 '11 at 23:50
  • You can also remap the logins to the users to be sure that the SID is correctly map. To remap a login use ALTER LOGIN msdn.microsoft.com/en-us/library/ms189828.aspx
    – Nico
    Apr 19 '11 at 23:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.