Role to grant a DBA who should not access confidential Database I am a junior DBA and I am in a bit of a pickle. I need to grant elevated privileges to our new DBA but he hasn't signed consent forms yet so I cannot grant him access to a specific database in our server.
How can I grant someone enough access to fulfill a DBA role but not access to one specific database?
As in, I don't want to add the person to sysadmin role which will give him access to this confidential database, but I also want him to be able to work (do his dba job that he was hired for).
Thank you
I suggest that you are careful with securityadmin.
As a security admin, you can create a new login and give this new login CONTROL permissions on the server. Now you can login as this new login and do almost anything. CONTROL on server is very close to sysadmin. Among other things, you can use any database and access all information, unless there are explicit DENY
Checking DENY is one of the big differences between sysadmin and CONTROL server. SQL Server doesn't even check for DENY for a sysadmin. But it does that for CONTROL server.
So, initially I thought that you could grant CONTROL server to this person, create a user in the confidential database, and then DENY CONNECT to this user. However, since this now can create a a login this can create a new login and grant CONTROL server to this new login. So, you would have to DENY ALTER ANY LOGIN to avoid this part.
In short: create a login, grant CONTROL SERVER (or securityadmin, or something else except sysadmin), deny ALTER ANY LOGIN, create a user in your confidential database, and deny connect for this login to the user in the confidential database.
This is just my own reasoning, so I can't say that there aren't any holes in above!