Security roles

Question

Security roles

as_1234

SSCrazy

Points: 2842
More actions
February 14, 2019 at 9:36 am

#382751

All,
Firstly I'm not necessarily asking for a complete answer. I'm happy to be pointed in the right direction to solve my own issue.
I've created a new user on the database. The database has a master key.
If I understand correctly the user needs the control permission to use the master key? That seems to give them more permissions than required so I'm wondering if I misunderstood?
Also working through SSMS it seems that I have to grant them the db_owner membership role for them to be able to access the key which doesn't seem correct.
I think to solve all of this I probably need to issue some permissions through sql commands rather than SSMS?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

as1981 - Thursday, February 14, 2019 9:36 AM
All,
Firstly I'm not necessarily asking for a complete answer. I'm happy to be pointed in the right direction to solve my own issue.
I've created a new user on the database. The database has a master key.
If I understand correctly the user needs the control permission to use the master key? That seems to give them more permissions than required so I'm wondering if I misunderstood?
Also working through SSMS it seems that I have to grant them the db_owner membership role for them to be able to access the key which doesn't seem correct.
I think to solve all of this I probably need to issue some permissions through sql commands rather than SSMS?
Thanks

A user normally doesn't directly interact with the database master key. If the database master key is encrypted with the service master key, it will automatically be used with the encryption, decryption process. Is there something in particular the users are doing that they need access to the database master key?

Sue

as_1234 SSCrazy Points: 2842 More actions · Answer 2

Hello,

Thanks for your reply, The application, that I intend this account to be used for, opens the master key to decrypt data with a certificate. From memory I had to use a master key rather than the service master key as it's an availability group but I'd need to check back on my reasons for using the master key.

Thanks

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 3

I don't think you need to grant someone control. The issue is that both the nodes (or all the nodes) need to be able to open the DMK. Usually the Service Master Key does this. You could restore the same one on all nodes, but likely you can create a credential to open the key as part of the database with sp_control_dbmasterkey_password

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-control-dbmasterkey-password-transact-sql?view=sql-server-2017

Then users can just access the certificate and provide the password to decrypt it.

as_1234 SSCrazy Points: 2842 More actions · Answer 4

Hello,

Sorry for not replying. I got distracted by some other issues and I've also been trying to answer a further question myself but unfortunately I've not succeeded. Currently the code is as follows:

open master key decryption by password ='PASSWORD' (password isn't the real password) open symmetric key keyname decryption by certification (not the real key name) Select distinct cast(DECRYPTBYKEY(somefield) as varchar(50)) from sometable

It's the open master key statement that's requiring the control permissions. Does using open master key with a password created by sp_control_dbmasterkey_password mean that it requires less permissions or do I need to change something else as well?

Thanks

goher2000 SSCertifiable Points: 5477 More actions · Answer 5

goher2000

SSCertifiable

Points: 5477

March 11, 2019 at 1:18 pm

#2024135

create stored procedure with execute as ??

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 6

If you set the master key to be encrypted by the SMK, it should be opened automatically. Disconnecting that is usually something you do when you do not wantt he sa/DBA to be able to decrypt the master key and get to data. However, that requires lots of admin headaches.