SQL Server Windows Authentication with Users and Groups


By:   |   Updated: 2020-12-29   |   Comments   |   Related: More > Security


Problem

One of the best ways to keep SQL Server secure is to use Windows Authentication. This requires the integration of Active Directory (AD) security objects like Users and Groups into SQL Server. The purpose of this tip is to provide a scalable and manageable approach that takes into consideration the ever-increasing number of instances and databases that you need to manage in your environment and how this security approach can make managing SQL Server security much easier.

Solution

For the purpose of this tip, I will use the following SQL Server instance names: INS001 and INS002.

We will use the AdventureWorks sample database to apply our security settings.  Below you can see that this has been restored as AdvWorks.

object explorer

Just a word of warning, I will be using a domain controller later on in the tip.

Configure Active Directory Groups and Users

In my local Active Directory "MyDomain.local", I have setup a folder called SqlServerUsersAndGroups with the following Users and Groups:

directory users

These groups and users should be sufficient for our demonstration.

Users now have to be assigned to their corresponding Active Directory groups. Right click on MsSqlUser001 and select "Add to a group…" as shown below:

active directory groups

Choose SqlReaderGroup for MsSqlUser001 as shown below:

select groups

For MsSqlUser002 add to SqlDeveloperGroup and for MsSqlUser003 add to SqlDBAGroup.

Step 1: Administrating access at the SQL Server Instance Level

SQL Server security has many layers and one of them is at the instance level. For every AD object (user or group) that needs access to the SQL Server instance a login is required within SQL Server. For the scalability of SQL Server access, I strongly recommend only adding Active Directory groups and avoid user logins. Using just groups provides a very robust approach when managing access to hundreds of users. Furthermore, one user can have multiple group memberships in AD.

Creating SQL Server Logins

The following SQL command adds the three AD groups mentioned earlier to the SQL Server instance:

USE [master]
GO
 
CREATE LOGIN [MYDOMAIN\SqlDBAGroup]       FROM WINDOWS
CREATE LOGIN [MYDOMAIN\SqlDeveloperGroup] FROM WINDOWS
CREATE LOGIN [MYDOMAIN\SqlReaderGroup]    FROM WINDOWS
GO 

Once the command is run, your Logins folder in SSMS should look like this where we can see the three new logins:

sql dba group

Remember to do this for all of the SQL Server instances (INS001, INS002).

Create SQL Server Roles and Add Members

For the SqlDBAGroup, we will can use Server Roles to give permission at the server level. You have the option to either use the Fixed Server-Level Roles or create your own custom Server-Level Role. One benefit is that with the custom role, you can design very specific privileges precisely for your system. You can learn more about User Defined Server Roles here.

Let's create a User Defined Server-Level Role that has the ability to create and drop databases as well as administer logins. Additionally, it needs the ability to connect to server and view definitions.  The following code will grant these permissions.

USE [master]
GO
 
CREATE SERVER ROLE [udsr_dba] – User Defined Server Role DBA
GO
 
GRANT ALTER ANY DATABASE    TO [udsr_dba]
GRANT ALTER ANY LOGIN       TO [udsr_dba]
GRANT ALTER ANY SERVER ROLE TO [udsr_dba]
GRANT CONNECT ANY DATABASE  TO [udsr_dba]
GRANT CONNECT SQL           TO [udsr_dba]
GRANT CREATE ANY DATABASE   TO [udsr_dba]
GRANT VIEW ANY DATABASE     TO [udsr_dba]
GRANT VIEW ANY DEFINITION   TO [udsr_dba]
GRANT VIEW SERVER STATE     TO [udsr_dba]
 
GO

Add SqlDBAGroup to the new user defined server role udsr_dba as follows:

ALTER SERVER ROLE [udsr_dba] ADD MEMBER [MYDOMAIN\SqlDBAGroup]GO

Step 2: Database Level Security

One level below SQL Server instance security is database level security. The difference between the two is that instance security defines rights and permissions for the whole SQL Server instance, whereas database security is specific to an individual database and can provide specific permissions to each database object, such as a table, stored procedure, view, etc.

The SQL Server database security has two types of objects "Users" and "Roles". By default, these look as follows in the AdvWorks database:

database security

Create Database Users

The way Database Security works is that a SQL Server login maps to database user in a database. Database users in turn can have role membership in none, many, or several database roles. Database users also can own none, one or many schemas in the database, but this is not a direct security feature. Owning a schema also grants ownership to schema objects and suppresses permission checking.

Let's illustrate database security in practice by adding our new logins to database AdvWorks and assign them their corresponding roles:

USE [AdvWorks]
GO

CREATE USER [SqlDBA]       FOR LOGIN [MYDOMAIN\SqlDBAGroup]
CREATE USER [SqlDeveloper] FOR LOGIN [MYDOMAIN\SqlDeveloperGroup]
CREATE USER [SqlReader]    FOR LOGIN [MYDOMAIN\SqlReaderGroup]
GO

Once created, the list of database users will look like this:

security

Create Database Roles

Our AdvWorks database has corresponding users for every new SQL Server login but the only permission in the AdvWorks database these users have now is the default "Public" role.

In order to grant data access, let's create database roles with custom access levels and assign membership to those roles:

USE [AdvWorks]
 
CREATE ROLE [db_sql_reader]
CREATE ROLE [db_sql_developer]
CREATE ROLE [db_sql_dba]
GO

Assign Permissions to Roles

To make this case realistic and to differentiate the db_sql_reader role from db_datareader, I would like this role to have read access to all objects in SalesLT schema and avoid the dbo schema as it contains system information that is more relevant to a DBA. In addition, it would be beneficial to grant permissions to execute stored procedures.

This can be done by allowing a general grant execute and grant select to the SalesLT schema for the db_sql_reader role:

USE [AdvWorks]
GRANT EXECUTE ON SCHEMA::[SalesLT] TO [db_sql_reader]
GRANT SELECT  ON SCHEMA::[SalesLT] TO [db_sql_reader]
GO

The db_sql_developer developer database role needs full access to the database, but assigning developers the db_owner database role will give more permissions than developers need, so we will limit permissions with the role.

The permission developers typically need are the following: select, delete, update, insert and execute on the SalesLT schema:

USE [AdvWorks]
GRANT EXECUTE ON SCHEMA::[SalesLT] TO [db_sql_developer]
GRANT SELECT  ON SCHEMA::[SalesLT] TO [db_sql_developer]
GRANT INSERT  ON SCHEMA::[SalesLT] TO [db_sql_developer]
GRANT UPDATE  ON SCHEMA::[SalesLT] TO [db_sql_developer]
GRANT DELETE  ON SCHEMA::[SalesLT] TO [db_sql_developer]
GO

The db_sql_dba database role requires access to error logs and other system data located in dbo schema and be done as follows:

USE [AdvWorks]
GRANT EXECUTE ON SCHEMA::[dbo] TO [db_sql_dba]
GRANT SELECT  ON SCHEMA::[dbo] TO [db_sql_dba]
GRANT INSERT  ON SCHEMA::[dbo] TO [db_sql_dba]
GRANT UPDATE  ON SCHEMA::[dbo] TO [db_sql_dba]
GRANT DELETE  ON SCHEMA::[dbo] TO [db_sql_dba]
GO

Once the code has been executed, the database roles folder should look as follows:

database roles

Assign Roles to Users

It is time now to assign database users to respective database roles. I would recommend assigning only one corresponding database role to a database user to keep a one-to-one relationship. Keep one-to-many relationships between users and groups in Active Directory.

USE [AdvWorks]
 
ALTER ROLE [db_sql_dba]       ADD MEMBER [SqlDBA]
ALTER ROLE [db_sql_developer] ADD MEMBER [SqlDeveloper]
ALTER ROLE [db_sql_dba]       ADD MEMBER [SqlDBA]
GO

You can check the database role membership by right clicking on a database user and selecting properties.  For example, we can see this for user SqlDeveloper below that has access to the role db_sql_developer.

database user

This completes the setup of database and server security.

Verification of SQL Server Security Settings

Table access verification can be performed with the help of system store procedure sp_table_privileges. This store procedure returns the list of all table permissions. You can find more details on the sp_table_privileges store procedure here.

The below T-SQL script returns table privileges for all tables in the database:

CREATE TABLE #TempTablePrivileges
(
  TABLE_QUALIFIER nvarchar(255),
  TABLE_OWNER nvarchar(255),
  TABLE_NAME nvarchar(255),
  GRANTOR nvarchar(255),
  GRANTEE nvarchar(255),
  PRIVILEGE nvarchar(255),
  IS_GRANTABLE nvarchar(255)
)
 
USE AdvWorks
GO
 
INSERT INTO #TempTablePrivileges
EXEC sp_table_privileges @table_name = '%%'
 
SELECT * FROM #TempTablePrivileges
 
DROP TABLE #TempTablePrivileges 

The script returns all table privileges including system ones like dbo and INFORMATION_SCHEMA and sys. If you are only looking for user defined table permissions, exclude these results from the query above. This tip has not granted any specific table privileges; hence this query should not return any user defined table permissions.

Schema privileges can be checked using sys.database_permissions in with the query below:

USE AdvWorks
 
SELECT 
   permission_name, 
   state_desc, 
   obj.name AS schema_name, 
   prin.name AS principal_name, 
   type_desc AS principal_type  
FROM sys.database_permissions as db_perm
JOIN sys.schemas as obj ON db_perm.major_id = obj.schema_id
JOIN sys.database_principals prin ON prin.principal_id = db_perm.grantee_principal_id
WHERE class = 3 -- SCHEMA ONLY 

We can check actual access rights of each individual server login by switching to a specific login and then trying to execute a statement. To do this, we use the EXECUTE AS statement.

See the code example below:

USE AdvWorks
 
EXECUTE AS LOGIN = 'MYDOMAIN\MsSqlUser001'
GO
 
SELECT * FROM [dbo].[BuildVersion]
 
REVERT;

More detailed information on EXECUTE AS can be found here. In our case, the select permission will be denied to MsSqlUser001 as it is only a member of SqlReaderGroup in Active Directory and this group is only given read and execute permissions to the SalesLT schema in our database. Therefore, select permissions is denied on dbo schema as shown below:

error message

Migrating database from one SQL Server instance to another

Now that security architecture is done for SQL Server INS001, we can demonstrate a migration of the AdvWorks database to instance INS002. This instance, in our case is installed on the same virtual machine node. Such migrations typically performed in large database development projects where the same database is copied between Development, Test and Production SQL Server instances. Before you proceed, please check that the security set-up of your server instances is identical as shown in step 1.

The simplest method to migrate a database is using backup and restore. If you do not know how to back up your database from one instance to another, you can find more help in this tip.

Restore this database to INS002.

Here is a sample database restore script:

USE [master]
RESTORE DATABASE [AdvWorks] 
FROM DISK = N'C:\MSSQL\Backup\AdvWorks.bak' 
WITH FILE = 1,
MOVE N'AdventureWorksLT2012_Data' TO N'C:\MSSQL\DATA\AdventureWorksLT2012.mdf',
MOVE N'AdventureWorksLT2012_Log' TO N'C:\MSSQL\DATA\AdventureWorksLT2012_log.ldf',
NOUNLOAD, 
STATS = 5
GO

Change the folder path to suit the environment you have created. More help on database restores can be found in this tip.

If the SQL Server logins have been created before we restore the AdvWorks database in INS002, the database users will be correctly mapped to their corresponding SQL server logins. If not, you will need to manually assign users to logins as follows which can be done with this script.

USE [AdvWorks]
GO
 
ALTER USER [SqlReader]    WITH LOGIN=[MYDOMAIN\SqlReaderGroup]
ALTER USER [SqlDeveloper] WITH LOGIN=[MYDOMAIN\SqlDeveloperGroup]
ALTER USER [SqlDBA]       WITH LOGIN=[MYDOMAIN\SqlDBAGroup]
GO
Next Steps


Last Updated: 2020-12-29


get scripts

next tip button



About the author
MSSQLTips author Semjon Terehhov Semjon Terehhov is an MCSE in Data Management and Analytics, Data consultant & Partner at Cloudberries Norway.

View all my tips
Related Resources



Comments For This Article





download





Recommended Reading

Enabling xp_cmdshell in SQL Server

Understanding SQL Server fixed database roles

Steps to Drop an Orphan SQL Server User when it owns a Schema or Role

How to configure SSL encryption in SQL Server

SQL Server Permissions List for Read and Write Access for all Databases














get free sql tips
agree to terms