Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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..

share|improve this question

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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