Tuesday, October 27, 2009

SQL Server Security Using Active Directory - Windows Authentication

The final post in my series dealing with only assigning rights directly to resources once deals with SQL Server security. My preferred method of assigning rights in SQL Server is very similar to my method for objects. However, database security and NTFS security are quite a bit different so I still feel the need to explain it.

First of all, I create 'Domain Local' groups (If I remember correctly, your domain needs to be at a certain functional level in order to use 'Domain Local' groups. So, if you have any problems assigning 'Domain Local' groups, you may want to check out what functional level you are at.) for all of the server roles as follows:

PROVO-SQL1[_Instance].ServerRole.bulkadmin
PROVO-SQL1[_Instance].ServerRole.sysadmin
PROVO-SQL1[_Instance].ServerRole.serveradmin
PROVO-SQL1[_Instance].ServerRole.setupadmin
PROVO-SQL1[_Instance].ServerRole.securityadmin
PROVO-SQL1[_Instance].ServerRole.processadmin
PROVO-SQL1[_Instance].ServerRole.dbcreator
PROVO-SQL1[_Instance].ServerRole.diskadmin

Now, even though you won't have very many people in any of these roles, I still follow my rule of never adding users to 'Domain Local' groups. I always create 'Global' groups based on job roles.

Next, I create groups for database level roles that will be applied to all databases as follows:

PROVO-SQL1[_Instance].AllDatabases.Read
PROVO-SQL1[_Instance].AllDatabases.Write
PROVO-SQL1[_Instance].AllDatabases.ReadWrite
PROVO-SQL1[_Instance].AllDatabases.Execute
PROVO-SQL1[_Instance].AllDatabases.Owner

These groups will then be given access to the corresponding database role in each database (e.g. PROVO-SQL1[_Instance].AllDatabases.Read will be assigned the db_datareader role). You will notice that I have added an AllDatabases.Execute. There is no db_executor role in SQL 2005, but it is easy enough to create one. Here is how I choose to accomplish this:

CREATE ROLE db_executor
GRANT EXECUTE TO db_executor (or GRANT EXECUTE on schema::dbo TO db_executor if you choose to grant at the schema level)
exec sp_addrolemember 'db_executor','YourUser'

I must give credit to the following posts http://www.eggheadcafe.com/software/aspnet/29440855/security-hole-with-dbexe.aspx for this informaiton.

The last set of groups created are specific to the database (could be specific to the schema if you wanted to break it out further) and are as follows:

PROVO-SQL1[_Instance].[Database].Read
PROVO-SQL1[_Instance].[Database].Write
PROVO-SQL1[_Instance].[Database].ReadWrite
PROVO-SQL1[_Instance].[Database].Execute
PROVO-SQL1[_Instance].[Database].Owner

We could get a lot more detailed here and add additional roles and schemas, but I think that this is a good enough explanation of the concept and the additional roles/schemas could be accounted for with additional descriptive groups.

The server role groups only need to be added to SQL once. The AllDatabases groups can be added to the model database to take care of any new databases. We use a custom stored procedure we created to add the database groups to any newly created databases.

Restricted Groups - Click Here
Object Rights Assignment - Click Here

No comments:

Post a Comment