Database permissions
Published 12 March 2019
You need to specify appropriate permissions for a user that is used to run the Windows service, as described in the Requirements.
Servers with databases that contain data to be classified
The user needs to have access to the schema definition of databases that contain data to be classified.
If data scanning is enabled for the instance where the database resides then db_datareader role is required.
Below is the set of permissions that is required:
USE [master] GO CREATE LOGIN [YOUR-DOMAIN\SqlDataCatalogUser] FROM WINDOWS WITH DEFAULT_DATABASE=[master] GO GRANT CONNECT SQL TO [YOUR-DOMAIN\SqlDataCatalogUser] GRANT CONNECT ANY DATABASE TO [YOUR-DOMAIN\SqlDataCatalogUser] -- For SQL 2012 + GRANT VIEW SERVER STATE TO [YOUR-DOMAIN\SqlDataCatalogUser] GRANT VIEW ANY DEFINITION TO [YOUR-DOMAIN\SqlDataCatalogUser] GRANT VIEW ANY SENSITIVITY CLASSIFICATION TO [YOUR-DOMAIN\SqlDataCatalogUser] -- For SQL 2019 + GO
Server with the SQL Data Catalog backing database
SQL Data Catalog uses a SQL Server database to store all data, including taxonomy and classification.
The user needs to have at least permissions to read and write data, as well as modifying schema of the database.
The simplest way is to just grant a dbcreator
role:
ALTER SERVER ROLE [dbcreator] ADD MEMBER [YOUR-DOMAIN\SqlDataCatalogUser]
If you don't want to grant a dbcreator
role, you can create a database manually and then apply more granular permissions:
CREATE DATABASE [Redgate_Classification] GO USE [Redgate_Classification] GO CREATE USER [YOUR-DOMAIN\SqlDataCatalogUser] FOR LOGIN [YOUR-DOMAIN\SqlDataCatalogUser] WITH DEFAULT_SCHEMA=[dbo] GO ALTER ROLE [db_ddladmin] ADD MEMBER [YOUR-DOMAIN\SqlDataCatalogUser] ALTER ROLE [db_datareader] ADD MEMBER [YOUR-DOMAIN\SqlDataCatalogUser] ALTER ROLE [db_datawriter] ADD MEMBER [YOUR-DOMAIN\SqlDataCatalogUser] GO USE [master] GO GRANT CONNECT SQL TO [YOUR-DOMAIN\SqlDataCatalogUser] GO