Restrict SQL admin user

We have a user who administers our SQL server.  He currently has the domain admin user account.  We would like to restrict him to the SQL server and a shared folder on another server.  What is the best way of loking him out of all other functionality.  He will probably need to log in via RDP.

Thanks
LVL 1
kaparaAsked:
Who is Participating?
 
nmcdermaidCommented:
No he can perform 95% of database administration through the SQL Server service. No one needs any file access to anything.

If your DBA needs to move or defrag the db files he will need access, but this really doesn't happen very often. If you DBA is just maintaining tables and indexes and stuff then he will be fine through the client tools.

0
 
nmcdermaidCommented:
If this is SQL2000, yuo have some options with varying levels of security:

Option A:

1. Just make him local administrator on the SQL Server

Option B:
1. Give him a standard windows account
2. Add that windows account as a login to the SQL Server
3. Add him to the 'System Administrators' role in SQL Server


Both of these options will give him administrative rights for the SQL Server as far as DBA tasks go.

Option A will of course also give him administrative rights to the entire SQL Server


In addition you will also need to give him access to the shared folder.
0
 
kaparaAuthor Commented:
The SQL server is also a DC.
0
 
nmcdermaidCommented:
So I assume that means option B.

If you take option B then in actual fact you don't even need to set up RDP, he can just use standard SQL Server client tools from a workstation.

It depends on exactly what DBA tasks he's doing as to whether he needs to log on to the server.

0
 
kaparaAuthor Commented:
Will he need direct access to the folder where the db files are located or can he do everything he needs from the SQL client tools or sql manager?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.