SQL Server: Setting Domain User Permissions

If you are new to SQL Server, you may be wondering how to quickly assign Domain User Permissions so that they can access the data within your database.

If you are not on a Windows Domain Network, this article is not for you.

There are two types of permissions that can be set (Server; Database) and two types of Authentication (Windows; SQL Server).

I’ll keep it simple. Since we are interested in granting Domain Users access to a specific database, we will be using “Windows Authentication” and “Database Permissions”.

If you are familiar with using Groups to set resource permissions (such as file shares) within a Domain environment, you will be fine with this process.

In preparation, the first step will be to create a “Domain Local” Group (Using “Active Directory Users and Computers”) on your Domain Controller (I’ve called mine “DL_Databases”). Include any “Global Groups” that require access to the database as members.

SQL Permissions 003

In the main part of this article, we will now proceed to grant SQL Database Read / Update permissions to the “DL_Databases” Group and all of its members.

As with most aspects of SQL Administration, we will be using SSMS (SQL Server Management Studio) to set the permissions we require.

Before you make changes to any live / production database, ensure that the data / schema is fully backed up!

Ok, let’s begin.

Using SSMS (SQL Server Management Studio), connect to the SQL Server Database Instance you wish to work on. Expand the appropriate database container and right-click on “Security”.

Select New => User.

SQL Permissions 001

TYPE the name of your “Domain Local” Group (including the Domain Prefix) into both the “User Name” and “Login” fields. You won’t be able to pick the names from a list, you must type them in!

SQL Permissions 002

Ensure that “db_datareader” and “db_datawriter” are both checked within “Role Members”. Ignore the “Owned Schemas” Box. Click “OK” and you’re all done.

Any Domain Users / Groups that are members of the “DL_Databases” group will now have Read / Write permissions to your SQL Database.

SQL Permissions 004

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s