Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
I got a call from someone wanted to check how permissions were stored for encryption objects. I ran a quick double check for them and decided to write this short post.
Let’s say that you create a few encryption keys. In my case, I’ll use this code to create a symmetric key, an asymmetric key, and a certificate.
CREATE SYMMETRIC KEY MySalaryProtector WITH ALGORITHM = AES_256, IDENTITY_VALUE = 'Salary Protection Key', KEY_SOURCE = N'Keep this phrase a secr#t' ENCRYPTION BY PASSWORD='Us#aStrongP2ssword'; GO CREATE ASYMMETRIC KEY HRProtection WITH ALGORITHM = RSA_2048 ENCRYPTION BY PASSWORD = 'Use4SomeStr0ngP@ssword%^'; GO CREATE CERTIFICATE MySalaryCert ENCRYPTION BY PASSWORD = N'UCan!tBreakThis1' WITH SUBJECT = 'Sammamish Shipping Records', EXPIRY_DATE = '20161231'; GO
I do this, I have these objects.
Let’s now grant rights to these objects. I’ll use this code to grant CONTROL to a user.
GRANT CONTROL ON SYMMETRIC KEY::MySalaryProtector TO JoeDBA GRANT CONTROL ON ASYMMETRIC KEY::hrprotection TO JoeDBA GRANT CONTROL ON CERTIFICATE::MySalaryCert TO JoeDBA
Once I do this, I should see permissions, right? Let’s check.
I don’t see any permissions in the dialog above. That’s not exactly what I’d want to see. After all, if I’m trying to determine why a user can’t access a certificate, I’d want to know if they had rights here.
Instead of this, I need to use T-SQL, and check for specific classes in sys.database_permissions. Here’s the query looking for class 24 (symmetric keys), 25 (certificates) and 26 (asymmetric keys).
You can see that I have permissions in here, and if I check the principal_id, I’ll find these are for my user. I could also join to database_principals and get specific information for my user.
This took a bit longer as someone asked me a question and I didn’t know the answer. I had to dig and read some documentation, but I found some answers and documented things myself.
Learned something, showed it, and hopefully will remember it from now on.