A Limited User for SQL Data Catalog

One of the things I’ve pushed for at Redgate is to document the minimum permissions needed for our products inside a database. Often this isn’t possible as some of our products require sysadmin, but some don’t. I was resetting Data Catalog up on a new machine and decided to limit things.

Installation

The installation does create a database, which you can do separately, but if you don’t do this, you need to grant dbcreator for the service account, at least for the install. If you create it, this page shows the more granular permissions if you want to do that.

The permissions needed are:

  1. ALTER ROLE [db_ddladmin] ADD MEMBER
  2. ALTER ROLE [db_datareader] ADD MEMBER
  3. ALTER ROLE [db_datawriter] ADD MEMBER
  4. GRANT CONNECT SQL

These permissions allow the service to write data to the various objects in the database.

Classifying Databases

Classifying databases is different. For this task, the account used to connect, which doesn’t need to be the service account, will read information about the tables, but does not read data. We just need to capture the column name and some metadata.

Again, this page gives a script, but essentially, we need these permissions.

    1. GRANT CONNECT SQL
    2. GRANT CONNECT ANY DATABASE (2012+)
    3. GRANT VIEW SERVER STATE
    4. GRANT VIEW ANY DEFINITION
    5. GRANT VIEW ANY SENSITIVITY CLASSIFICATION (2019+)

Once those permissions are granted to an account, you use the account to add the instance to SQL Data Catalog.

I’m glad that we’re documenting and limiting permissions where possible. We do understand data privacy and protection are important, and the advocates at Redgate try to push developers to use as limited permissions as possible when building products.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.