Checking Permissions for Keys–#SQLNewBlogger

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.

2016-11-29 14_21_19-SQLQuery11.sql - 192.168.1.204_SQL2016.EncryptionDemo (sa (57))_ - Microsoft SQL

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.

2016-11-29 14_25_30-Database User - JoeDBA

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

2016-11-29 14_27_57-SQLQuery11.sql - 192.168.1.204_SQL2016.EncryptionDemo (sa (57))_ - Microsoft SQL

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.

2016-11-29 14_30_41-SQLQuery11.sql - 192.168.1.204_SQL2016.EncryptionDemo (sa (57))_ - Microsoft SQL

#SQLNewBlogger

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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.