Need help with User, Login, Security - Win2k vs SQL security

Question

Need help with User, Login, Security - Win2k vs SQL security

Brad Russell

Newbie

Points: 2
More actions
July 21, 2005 at 11:50 am

#86864

Hello all,
I'm a dba who has some experience creating roles and users and defining permissions in the SQL security system.
However, now I am faced with something different: Windows 2000 security.
We have 10 people who are identified by the server as administrators, giving them unlimited rights in SQL. They log on using windows security. There are two of these who need to have their rights restricted so that they cannot delete stored procedures. My inclination is to create a database role called "CANT_TOUCH_THIS" and deny the right to delete stored procs to that role, then add the two individuals to that role. But since they log in using windows security, how do I find them to add them to the role?
I'm sure that this is obvious to anyone familiar with the Windows security system, but I've looked on line and in all my books and haven't been able to figure it out yet. Any tips?
Thanks in advance for any advice.

Viewing 4 posts - 1 through 4 (of 4 total)

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

K. Brian Kelley SSC Guru Points: 114704 More actions · Answer 1

Better yet, remove the BUILTIN\Administrators group from having sysadmin rights. Be sure your DBAs have rights through another group. Issues you need to be aware of here:

SQL Server Security: Security Admins

K. Brian Kelley
@kbriankelley

Brad Russell Newbie Points: 2 More actions · Answer 2

Thanks for the reply -

This is in a development environment, and although what you've suggested would be apropriate for production, management doesn't want this done in the development environment.

Also, I will need to know how to assign other Windows network users to SQL server roles. This ought to be do-able in Enterprise Manager as well as Query Analyzer. I just haven't found it yet.

Any suggestions?

K. Brian Kelley SSC Guru Points: 114704 More actions · Answer 3

It's better to remove the BUILTIN\Administrators and then add back in groups which need permission than to start issuying denys. About the only you can do is specifically deny a login but then you block them from SQL Server, period, hence the problem.

As to how to do it in Enterprise Manager....

1) Expand the server.

2) Expand Security.

3) Right-click on Logins and select New Login

4) Enter the Windows login in the form Domain\User or Domain\Group.

5) Assign roles and permissions as appropriate.

K. Brian Kelley
@kbriankelley