Q: 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

A: 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!

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

You are absolutely correct.
Until they have agreed to everything that your Organisation requires them to agree to before giving them the Keys to the Castle, then they don't get the keys.  Period.
You would be negligent if were to do so.

... enough access to fulfill a DBA role ...

These are the horns of your particular dilemma.
How much of what do they need to do their job?
One solution would be to look at this temporaneously:

*

*What do they need to get started, right now?


*What will they need to do the job properly, assuming that all the paperwork goes through?
I strongly suspect that those two could be quite different.


*Do they appear reasonable and likely to accept a "phased" induction, getting enough access to "look" around, to get a feel for the "lay of the land" today but to wait for their real permissions "payload" to come along later?
Personally, I'd be quite happy not to have access to the Big Red Buttons until I knew what was what?

A: I think securityadmin is what should be good fit for you in the given scenario. This role wouldn't give explicit access to any user database however mentioned role can do DBA role for security related tasks except sysadmin activity.
Below are features of this role:

*

*This role controls logins for SQL Server.

*This role can grant access to databases within SQL Server.

*This role, by itself, cannot define any further security within a database.

*This is another good role for junior DBAs when sysadmin is too much. Because it is a security related role, membership in it should be carefully restricted,
especially in production.

In case, you wish to grant him/her more access on databases(except secure one), you could grant owner permission and this would pretty much do most of the things within database.
Below are features of owner role:

*

*The db_owner role allows a user to do anything within the database.

*DBAs who are already members of the sysadmin fixed server role come in as dbo and don't need this role explicitly granted to them.

*Normal users should not be a member of this role.

*Applications might require their user account to be a member of this role.

I hope this helps.
