Locking Down an individual database while leaving the others accessible 
Author Message
 Locking Down an individual database while leaving the others accessible

My company is bringing payroll in-house.  This has created a surge in interest as to how to lock this down to prevent everyone but a select few from being able to access this new database.  We run SQL Server 2000 Enterprise in a failover cluster [MSCS} environment.  In the white paper entitled "Microsoft SQL Server 2000 Security" it states that the recommended path is to place Windows user accounts into Global Groups within each domain.  Then local groups are created that the Global groups are added as members.  The local group then is granted access to SQL Server.  Finally, the local group is granted access to the database.  It also mentions roles and some other things that I do not really understand how to make do what I need so we will start with the groups.

  1.. In a clustered failover environment, where do the local groups go?  The physical server which would require both servers in the cluster to have this group or the virtual server thus only requiring a single local group definition.
  2.. How do you make the default access to a database deny?  Do you authorize the groups to have access? Then make the public role deny checked on all boxes similar to the everyone permissions in windows?
  3.. Among the select few to have access I will have some users that can read only, some that can Add, Change and Delete and the administrators that have full control.  Do you use separate groups for each or is this where roles come in?  I'm not real clear on roles.
  4.. I have users that are members of multiple groups.  For instance, a user can be a member of payroll and finance.  Payroll will have access to the database but most of the finance group will not.  If deny takes precedence over all other group authority, how do I secure some out and allow some in?  Please do not tell me to pull the users with access authority out of the other groups.  This will require a lot of work to research what other authorities they need in the access group and probably end up with one user per group which defeats the purpose.

Mon, 06 Sep 2004 23:28:36 GMT
 Locking Down an individual database while leaving the others accessible

Hi John,

Best to set up security by first removing PUBLIC role from having
permissions on the database.  This keeps wondering eyes out.

Next with SQL 2000 you can assign Domain\DomainGroups login permissions to
the database.  No need to create a local group.

In your case:

1) remove Public permissions from the database objects.
2) Create Domain groups for Payroll permissions to the database like
PayrollReadOnly, PayRollReadWrite and assign members as needed.
3) Make those in Finance who need access, make them members of the same
groups as Payroll.

No need to add the complexity of Deny unless for example you had a very
small group in Payroll who were not allowed in the database.  You could
make them a member of the PayrollDeny group.  For everyone since they do
not belong to a group that has permissions, they will not get in.

Best of luck,

Chris Skorlinski, MCDBA
Microsoft SQL Server Support

This posting is provided "AS IS" with no warranties, and confers no rights.

Tue, 07 Sep 2004 02:22:23 GMT
 [ 2 post ] 

 Relevant Pages 

1. Is leaving db open == leaving pants down?

2. DTS locks Access 97 database - and leaves it locked even once completed

3. IB4.2.2 Slows down on individual clients

4. One job slow down all others (SQL 7)...

5. Locking down database accounts

6. Locking Down a Web Database Server

7. How do I lock down a SQL database...

8. Obtaining an individual exclusive row lock

9. Locking Records so others can't read

10. Access97 to SQL7 -- intermittent long delays on 1 client - locks server for others

11. How to lock a table of Interbase to avoid that others modify it

12. missing process locked others

Powered by phpBB® Forum Software