By: Semjon Terehhov | Comments (3) | Related: > 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.
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:
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:
Choose SqlReaderGroup for MsSqlUser001 as shown below:
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:
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:
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:
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:
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.
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:
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
- Adding Users to Azure SQL Databases
- Querying Active Directory Data from SQL Server
- Querying Active Directory Data from SQL Server
- How to Setup Row Level Security for SQL Server
- SQL Server Security Audit Report
About the author
Semjon Terehhov is an MCSE in Data Management and Analytics, Data consultant & Partner at Cloudberries Norway.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
