SQL Server Login - Windows Groups

Question

SQL Server Login - Windows Groups

zulf_o

Grasshopper

Points: 23
More actions
September 23, 2010 at 8:13 am

#387849

We are trying to standardise access to SQL Server in our oraganisation.
We are installing SQL Server Management Studio locally for all our developers and DBA's.
The DBA login in sql server has been given the 'sysadmin' role. The DBA domain account has been made a member of the following Windows Groups,
- Remote Desktop Users
- Users
Our developers have been given the appropriate database role. Their domain admin account has been made a member of the following Windows Groups,
- Users
Can you advise whether this is a correct setup, if not can you suggest an alternative ?
Do we need to put Developers in the 'Users' group ?
Thanks

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

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

NJ-DBA SSChampion Points: 13834 More actions · Answer 1

So, to be sure I understand correctly, you are asking about the windows permssions required by your DBA and your users- not asking about database level permissions- correct?

Your DBA is probably going to need more than Users and Remote Desktop users on your SQL Server. Typically DBA would be in the local administrators group on the server.

Developers- probably will not need any access to the SQL Server at all. DBA should manage the developers rights at the SQL Server permssion level. So- they are granted rights to database, but not to the database server.

Am I correctly understanding your question?

zulf_o Grasshopper Points: 23 More actions · Answer 2

Yes that is correct. I am asking about the windows permission for the DBA

subbu_e SSCrazy Points: 2431 More actions · Answer 3

For DBA

--------------

From the Windows Perspective, he needs to able to login to the SQL Server Remotely and Able to access the Server Without any problem and he need to control or manage SQL Server Services too.

so he should be part of Local Admin so that he can install the SQL Services.

Needed Permissions are

1. Part of Local Administrator able to login through remotely.

2. You need to grant the SQL SErver permission too on those Groups (DBA Group)

For Developer

--------------

They will have to have the SQL Login alone, they don't need to access the Server remotely.

Note : if you have not Removed your built-in administrator all the local administrators/Groups will be able to gain the System Admin Privilege on your SQL box.

To avoid the , You can simply remove the System Admin permission on Builtin-Administrator on SQL Server Login. however still all the Local administrator able to login to the SQL Server.

Steps to Remove Builtin-Administrator

1. You need to have an system Administrator Account and check you are able to login to the SQL Server.

2.Make sure that SQL Service account also has the Permission

3.Remove the Builtin-Administrator.

Hope this helps you

Regards,
Subbu
Click here to Get Speedy answer or solution

Steve Jones - SSC Editor SSC Guru Points: 736211 More actions · Answer 4

Some places don't grant Windows admin to DBAs. I've had it both ways, and I like DBAs being able to see performance stuff from windows, and handle permission issues if needed.

I also like having Windows admins able to act as remote SQL DBAs if needed. It depends on what your requirements are, and how much you trust people.

No matter what you do, if DBAs make changes in Windows, or sysAdmins change SQL, they ought to be sure to document things and let the other group know what was done, and why.

subbu_e SSCrazy Points: 2431 More actions · Answer 5

Yes i agree to it

Regards,
Subbu
Click here to Get Speedy answer or solution

crazy4sql SSCoach Points: 19590 More actions · Answer 6

also in most of the case sql service are configured with windows account and that account need to be in domain users and domain admin account.

----------
Ashish

NJ-DBA SSChampion Points: 13834 More actions · Answer 7

SQL Service account should definitely NOT be a domain admin account! SQL Server will give the service account the minumum required permissions on the server. Domain admin is extremely highly privledged and to grant such access to the SQL Service account would be a security risk.