Compare SQL permissions using SQL Server Data Tools

Problem

One of the SQL Server DBA’s primary tasks is security and the review of users. Out of the box SQL Server does not provide tools for comparing permissions, you have to write scripts or use third party tools. In this article we will show how to use Visual Studio and SQL Server Data Tools to compare permissions and to copy permissions (including users and logins).

Solution

If you are not familiar with SQL Server Data Tools (SSDT) you can start by reading this tip. This tip includes steps to create your first “SQL Server Database Project”.

With the SSDT you can compare schema or data which are pretty common tasks in database development and change control. For this tip we will use the SSDT to compare logins, users, roles (including role membership) and permissions. We will use the SSDT’s “compare” feature.

Some of the other security related object types that are available under SSDT compare are:

• Application Roles
• Asymmetric Keys
• Certificates
• Symmetric Keys
• Credentials
• Database Encryption Keys
• Linked Servers
• Linked Servers Logins
• Master Key

Creating Example Data

Create two databases on two different instances (DEMOSRV3 and DEMOSRV4 SQL Server instances in our examples) using the following script:

USE master
GO
CREATE DATABASE [_DemoDB]
GO 
USE [_DemoDB]
GO
CREATE PROCEDURE dbo.demo_p_1
AS 
SELECT 1 as Col
GO
CREATE PROCEDURE dbo.demo_p_2
AS 
SELECT 2 as Col
GO
CREATE VIEW dbo.demo_v_1
AS 
SELECT 1 as Col
GO

To demonstrate several security related scenarios we will use the new “SQL Server Database Project” with the following database connections:

1. “Development Database” connection (DEMOSRV3) – Source database that has all logins, users, roles and permissions.
2. “Staging Database” connection (DEMOSRV4) – Database that has only schema objects and data (no users, roles, permissions).

Compare SQL Server Security Settings – Scenario 1

In this scenario we will compare logins, users, roles and role membership. We will generate a script to copy missing security objects to another instance.

Run this script on the Development instance (DEMOSRV3 SQL Server instance in our example) to create a login, role, user and grant server role and database role to the login/user:

USE [master]
GO
CREATE LOGIN [_demo_login] WITH PASSWORD=N'T3stL0g!n', DEFAULT_DATABASE=[master], 
CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
ALTER SERVER ROLE [bulkadmin] ADD MEMBER [_demo_login]
GO
USE [_DemoDB]
GO
CREATE ROLE [_demo_role]
GO
ALTER ROLE [db_datareader] ADD MEMBER [_demo_role]
GO
CREATE USER [_demo_user] FOR LOGIN [_demo_login] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [_demo_role] ADD MEMBER [_demo_user]
GO

In Visual Studio, open your Database Project, click “Schema Compare” under the “SQL” menu and then “New Schema Comparison…”:

You will get this window:

Click on the “Options” button (1) and select the following object types:

• Application-scoped:
  • Database Roles
  • Role Memberships
  • Users
• Non-application-scoped:
  • Logins
  • Server Roles
  • Server Role Membership

Select the Development Database (DEMOSRV3) as a source database (2) and the Staging Database (DEMOSRV4) as a target database (3).

Click the “Compare” button (4).

Click the “Generate Script” button as shown below:

and review the script that was generated:

/*
Deployment script for _DemoDB
This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/
GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, 
CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
SET NUMERIC_ROUNDABORT OFF;
GO
:setvar DatabaseName "_DemoDB"
:setvar DefaultFilePrefix "_DemoDB"
:setvar DefaultDataPath "X:\MSSQL\DATA\"
:setvar DefaultLogPath "Y:\MSSQL\TLOGS\"
GO
:on error exit
GO
/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF; 
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
    BEGIN
        PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
        SET NOEXEC ON;
    END
GO
USE [$(DatabaseName)];
GO
PRINT N'Creating [_demo_login]...';
GO
CREATE LOGIN [_demo_login]
    WITH PASSWORD = N'yxXvugfdu3UapzMmmXu2zo', SID = 0x4C06BCA98BD2094998B52518941AAB53, 
    DEFAULT_LANGUAGE = [us_english], CHECK_EXPIRATION = ON;
GO
PRINT N'Creating [_demo_user]...';
GO
CREATE USER [_demo_user] FOR LOGIN [_demo_login];
GO
PRINT N'Creating [_demo_role]...';
GO
CREATE ROLE [_demo_role]    AUTHORIZATION [dbo];
GO
PRINT N'Creating ...';
GO
EXECUTE sp_addrolemember @rolename = N'_demo_role', @membername = N'_demo_user';
GO
PRINT N'Creating ...';
GO
EXECUTE sp_addrolemember @rolename = N'db_datareader', @membername = N'_demo_role';
GO
PRINT N'Creating ...';
GO
ALTER SERVER ROLE [bulkadmin] ADD MEMBER [_demo_login];
GO

Note: the part of the script for the login creation keeps the same SID, but generates a random password.

Generate SQL Server Security Comparison Scripts – Scenario 2

In this scenario we will copy permissions.

Note, permissions are scripted only when the target object is selected (procedure, view, table etc.).

Run this script on the Development instance (DEMOSRV3 SQL Server instance in our example) to grant objects, database and server level permissions:

USE [_DemoDB]
GO
GRANT EXECUTE ON [dbo].[demo_p_1] TO [_demo_role]
GO
GRANT SELECT ON [dbo].[demo_v_1] TO [_demo_role]
GO
-- database level permissions
GRANT VIEW DEFINITION TO [_demo_user]
GO
-- server level permissions
use master
GO
GRANT VIEW ANY DEFINITION TO [_demo_login]
GO

Now go back to the Compare window and click on the “Options” button. Select the following object types:

• Application-scoped:
  • Permissions
  • Stored Procedures
  • Views
• Non-application-scoped:
  • Database Options, Permissions and Extended Properties

Click “Compare” and you will get these results:

Click on the “Generate Script” button to create the script as shown below:

/*
Deployment script for _DemoDB
This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/
GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, 
CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
SET NUMERIC_ROUNDABORT OFF;
GO
:setvar DatabaseName "_DemoDB"
:setvar DefaultFilePrefix "_DemoDB"
:setvar DefaultDataPath "X:\MSSQL\DATA\"
:setvar DefaultLogPath "Y:\MSSQL\TLOGS\"
GO
:on error exit
GO
/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF; 
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
    BEGIN
        PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
        SET NOEXEC ON;
    END
GO
USE [$(DatabaseName)];
GO
PRINT N'Creating Permission...';
GO
GRANT VIEW DEFINITION TO [_demo_user];
GO
PRINT N'Creating Permission...';
GO
GRANT EXECUTE ON OBJECT::[dbo].[demo_p_1] TO [_demo_role] AS [dbo];
GO
PRINT N'Creating Permission...';
GO
GRANT SELECT ON OBJECT::[dbo].[demo_v_1] TO [_demo_role] AS [dbo];
GO
PRINT N'Update complete.';
GO

Note, that the server level permission was not scripted. These permissions cannot be compared in SSDT, so you need to use T-SQL scripts. Here is an example of a T-SQL script that will show server level permissions:

SELECT l.name, pr.permission_name, pr.state_desc 
FROM sys.server_principals l JOIN sys.server_permissions pr
ON l.principal_id = pr.grantee_principal_id