Minimum Permissions for Scan Accounts

Connection Prerequisites

1. Connection test and database metadata

   Operation	Catalog views / DMVs read	Minimum grant
   Test connection		GRANT CONNECT TO … (db)
   Schema enumeration	sys.schemas	None additional (visible to public)
   Table/view listing & JDBC getTables/getColumns	sys.objects, sys.tables	Object/schema-level GRANT SELECT (covers metadata visibility)
   Primary/foreign key detection (JDBC getPrimaryKeys/getImportedKeys)	sys.indexes, sys.foreign_keys	Object/schema-level GRANT SELECT
   Approximate row count / table size	sys.dm_db_partition_stats, sys.partitions, sys.allocation_units, sys.indexes	GRANT VIEW DATABASE STATE (db) for the DMV; metadata visibility on the rest comes from object/schema SELECT

2. Data sampling

   Operation	Tables read	Minimum grant
   TABLESAMPLE PERCENT row sample	Target user table/view	Object/schema-level GRANT SELECT
   Column payload sampling	Target user table/view	Same as above

3. Trustee (principals & roles) extraction

   Operation	Catalog views read	Minimum grant
   List database users & roles	sys.database_principals	GRANT VIEW DEFINITION (db)
   Database-level role membership (recursive)	sys.database_role_members, sys.database_principals	GRANT VIEW DEFINITION (db)
   List server logins & server roles	sys.server_principals	GRANT VIEW ANY DEFINITION (server)
   Server-level role membership (sysadmin check)	sys.server_role_members, sys.server_principals	GRANT VIEW ANY DEFINITION (server)

   SELECT on the catalog views above is already granted to public. Rows are filtered by metadata visibility — VIEW DEFINITION (database scope) unlocks rows for database-level principals; VIEW ANY DEFINITION (server scope) unlocks rows for server-level principals.

4. Permissions extraction

   Operation	Catalog views read	Minimum grant
   Database-level permission assignments	sys.database_permissions, sys.database_principals	GRANT VIEW DEFINITION (db)
   Server-role membership (sysadmin etc.)	sys.server_role_members, sys.server_principals	GRANT VIEW ANY DEFINITION (server)

The scan account must be granted full read access to the entire datasource. This applies to all current and future user tables and views automatically and ensures cataloging and classification work without gaps.

ALTER ROLE [db_datareader] ADD MEMBER [db_fp_user_role];

(SQL Server 2008+. Sections 1 and 2 run in [master]; sections 3 onward run in the target database.)

-- 1) Server login (in [master])
USE [master];
GO
IF SUSER_ID(N'fp_user') IS NULL
BEGIN
    CREATE LOGIN [fp_user]
        WITH PASSWORD     = N'<STRONG_PASSWORD>',
             CHECK_POLICY = ON;
END
GO
ALTER LOGIN [fp_user] WITH PASSWORD = N'<STRONG_PASSWORD>';
GO

-- 2) Server-scope: trustee + sysadmin discovery (in [master])
GRANT VIEW ANY DEFINITION TO [fp_user];
GO

-- 3) Database user (in target database)
USE [<TARGET_DATABASE>];
GO
IF USER_ID(N'fp_user') IS NULL
    CREATE USER [fp_user] FOR LOGIN [fp_user];
GO

-- 4) Custom database role wrapping all db-scope grants
IF DATABASE_PRINCIPAL_ID(N'db_fp_user_role') IS NULL
    CREATE ROLE [db_fp_user_role];
GO
EXEC sp_addrolemember N'db_fp_user_role', N'fp_user';
GO

-- 5) Database-scope grants (always required)
GRANT CONNECT             TO [db_fp_user_role];   -- open JDBC session
GRANT VIEW DATABASE STATE TO [db_fp_user_role];   -- sys.dm_db_partition_stats
GRANT VIEW DEFINITION     TO [db_fp_user_role];   -- principals / role / permission catalogs
GO

-- 6) Data + metadata access — full read access to the entire datasource
ALTER ROLE [db_datareader] ADD MEMBER [db_fp_user_role];
GO

Run as fp_user to confirm each capability:

EXECUTE AS USER = N'fp_user';

-- Connection + grants overview
SELECT * FROM fn_my_permissions(NULL, N'DATABASE');

-- Metadata & data scan
SELECT TOP 1 * FROM sys.objects;                   -- schema/object SELECT (metadata)
SELECT TOP 1 * FROM sys.dm_db_partition_stats;     -- VIEW DATABASE STATE
-- SELECT TOP 10 * FROM [<schema>].[<table>];

-- Trustee scan
SELECT TOP 5 name, type_desc FROM sys.database_principals;
SELECT TOP 5 *               FROM sys.database_role_members;
SELECT TOP 5 name, type_desc FROM sys.server_principals;     -- VIEW ANY DEFINITION
SELECT TOP 5 *               FROM sys.server_role_members;   -- VIEW ANY DEFINITION

-- Permissions extraction
SELECT TOP 5 * FROM sys.database_permissions;

REVERT;