DBSheriff for SQL Server — prerequisites and accounts

DBSheriff connects with its own dedicated logins — never your application's credentials — each limited to what its job needs (the last section says exactly what each one can do if its password leaks). Setup has two parts: server prerequisites (Extended Events statement capture, SQL Server Audit) and the accounts themselves. Three scripts automate all of it. Supported: self-managed SQL Server 2016 SP1+ (2019/2022/2025 recommended), Amazon RDS for SQL Server, Azure SQL Database, and GCP Cloud SQL for SQL Server. Azure SQL Managed Instance takes the on-prem path unchanged (a real instance, not a PaaS database) and isn't called out separately below.

The three scripts

On an installed host there is no scripts/ folder, so read every scripts/<name>.py below as sudo /opt/dbsheriff/current/deploy/onprem/stack.sh tool <name> ..., for example sudo /opt/dbsheriff/current/deploy/onprem/stack.sh tool setup_dbsheriff mssql db1.acme.internal. The scripts/ form is for a source checkout. The details are under "How to start the scripts" in Before you install: creating DBSheriff's database users.

Script	What it does	Example
`setup_dbsheriff.py`	Recommended. One command: checks/enables the prerequisites you name with `--optional-prereqs` (for SQL Server both are optional), then creates the accounts.	`scripts/setup_dbsheriff.py mssql db1.acme.internal --optional-prereqs=extended_events,server_audit`
`install_prereqs.py`	Prerequisites only — run this alone if you want to review each step separately before creating any accounts.	`scripts/install_prereqs.py mssql db1.acme.internal`
`create_reset_dbuser.py`	Accounts only — creates or resets them once prerequisites are already in place.	`scripts/create_reset_dbuser.py mssql db1.acme.internal`

All three are interactive (they ask which platform, which accounts, and for database credentials), connect only as a database login — never a cloud credential — and never restart the database server. Add --dry-run to any of them to preview without changing anything. To create fewer than all five: pass --roles=monitor,kill to setup_dbsheriff.py, or answer create_reset_dbuser.py's own account-selection prompt with the numbers/names you want (or all).

Add --check to any of them for a read-only status report instead: what's already there, what's missing, and the exact command (or, where a platform genuinely can't do it, the manual fix) for each gap — nothing is ever written, not even if you say yes to something (there's no prompt to say yes to). Exits with a non-zero status if anything's missing, so it's usable in a script.

The 5 accounts

Login	Required?	Unlocks
`dbsheriff_monitor`	Required	Live requests/sessions, counters, logins, the error log, audit reads
`dbsheriff_kill`	Optional	Ending a malicious session
`dbsheriff_lock`	Optional	Locking a compromised login, the graduated response
`dbsheriff_posture`	Optional	A dedicated credential for posture checks + hourly governance collection (otherwise reuses `dbsheriff_monitor`)
`dbsheriff_classify`	Optional	Column metadata, statistics, existing classifications, value sampling

Creating the accounts

Almost identical on on-prem and Amazon RDS for SQL Server — RDS's master user (processadmin + setupadmin) holds every CREATE LOGIN/ GRANT below with grant option, confirmed live; the one RDS exception is called out inline below. GCP Cloud SQL and Azure SQL Database differ enough to need their own sections below — jump to yours.

USE master;
CREATE LOGIN dbsheriff_monitor  WITH PASSWORD = '<password>', CHECK_POLICY = ON, CHECK_EXPIRATION = OFF;
CREATE LOGIN dbsheriff_kill     WITH PASSWORD = '<password>', CHECK_POLICY = ON, CHECK_EXPIRATION = OFF;
CREATE LOGIN dbsheriff_lock     WITH PASSWORD = '<password>', CHECK_POLICY = ON, CHECK_EXPIRATION = OFF;
CREATE LOGIN dbsheriff_classify WITH PASSWORD = '<password>', CHECK_POLICY = ON, CHECK_EXPIRATION = OFF;
-- dbsheriff_posture: identical to dbsheriff_monitor (same statement, same grants)

GRANT VIEW SERVER STATE, VIEW ANY DEFINITION, VIEW ANY DATABASE TO dbsheriff_monitor;
GRANT ALTER ANY CONNECTION, VIEW SERVER STATE TO dbsheriff_kill;
GRANT ALTER ANY LOGIN, ALTER ANY CONNECTION, VIEW SERVER STATE TO dbsheriff_lock;

-- on-prem only: RDS's master user has no db_owner/db_accessadmin in
-- master (confirmed live: IS_ROLEMEMBER('db_owner') = 0 there), so this
-- fails outright on RDS (42000/15247) and isn't needed there anyway — RDS
-- reads the error log and the audit file from msdb/rdsadmin instead.
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'dbsheriff_monitor') CREATE USER dbsheriff_monitor FOR LOGIN dbsheriff_monitor;   -- catalog access + (on-prem) the audit reader

Then, per database to monitor:

USE <database>;
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'dbsheriff_monitor')  CREATE USER dbsheriff_monitor  FOR LOGIN dbsheriff_monitor;   -- no data access: catalog only
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'dbsheriff_kill')     CREATE USER dbsheriff_kill     FOR LOGIN dbsheriff_kill;      -- no permission in the database: lets the kill session open it
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'dbsheriff_classify') CREATE USER dbsheriff_classify FOR LOGIN dbsheriff_classify;
GRANT SELECT ON SCHEMA::dbo TO dbsheriff_classify;   -- every schema to scan

IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'dbsheriff_lock') CREATE USER dbsheriff_lock FOR LOGIN dbsheriff_lock;
ALTER ROLE db_securityadmin ADD MEMBER dbsheriff_lock;   -- DENY SELECT on a table, graduated response

List the databases to monitor in the registry row's scan_databases (default: the registry database). CHECK_EXPIRATION = OFF keeps a service password from expiring under the Windows policy.

Every login needs a user in the registered database, the kill login too. dbsheriff_kill holds no permission there (ALTER ANY CONNECTION is server-wide), but its kill session selects the registered database, and SQL Server refuses a login that has no user in the database it is asked to open (error 4060, "Cannot open database … requested by the login"). On an Azure SQL Managed Instance, an on-premises server and Cloud SQL, where the logins are server logins, the scripts create that user in every user database, on a create and again on a reset (create_reset_dbuser.py mssql <host>, answer "reset" when asked, or onboard_target.py --reset-existing-accounts), which repairs an account made before. A login that still has none is not out of work: DBSheriff's kill, the fast-path kill and the monitor's pool then use a session in master, which sees the same sessions on these servers, and write one warning per episode that names the login, the database and the statement that fixes it (USE [db]; CREATE USER [login] FOR LOGIN [login];). The database is tried again every ten minutes. Azure SQL Database is not affected: its users are contained in the database, and a master session sees none of its sessions, so nothing falls back there. The fallback covers the kill, the fast-path kill, the monitor's pool and the session probe only. The lock login does not fall back: on Azure SQL Database and on a Managed Instance its DENY CONNECT rung runs in the registered database, so it needs its own user there (the scripts create it), and a lock login without one cannot complete that rung: the lock result names DENY_CONNECT and the connection error (on a Managed Instance, 4060).

Never auto-executed — apply these by hand when you want them, the same "optional, not required" items the scripts print as comments rather than run:

GRANT EXECUTE ON sys.xp_readerrorlog TO dbsheriff_monitor;           -- on-prem only: failed-login fallback when the audit's login groups are off
GRANT EXECUTE ON sys.xp_readerrorlog TO dbsheriff_posture;           -- the same for the posture login, if you created it: the governance collector reads the error log with that login
GRANT VIEW ANY SENSITIVITY CLASSIFICATION TO dbsheriff_classify;      -- existing labels (SSMS Data Discovery & Classification, Purview) as evidence
GRANT ALTER ANY SENSITIVITY CLASSIFICATION TO dbsheriff_lock;         -- write-back after classification approval; also flip the mssql_tag_writeback switch

Prerequisites

Two items, neither needs a restart — ALTER EVENT SESSION ... STATE = START and ALTER SERVER AUDIT ... WITH (STATE = ON) both take effect immediately:

Extended Events statement capture (recommended — the only source that names the login on a completed statement; without it, completed statements fall back to sys.dm_exec_query_stats, which has no login column at all).
SQL Server Audit — server audit + specification (optional; logins, DDL, DCL). A per-table DML specification is never automated — it's customer-schema-specific; add one by hand once you know which tables to cover.

What a server without either item is not watched for. With no Extended Events session and no readable audit, DBSheriff reads completed statements from sys.dm_exec_query_stats, the plan cache. It is a statistics source, not an event log, and it has three limits that matter for security:

DDL and DCL are never seen (CREATE, DROP, ALTER, GRANT, DENY, REVOKE: these statements leave no plan-cache entry).
A statement is seen only while its plan is cached. One that leaves no plan, or whose plan is evicted before the next poll, is never seen, and a short statement run once is often one of them. The running-statement poll still catches a statement that is still executing when it reads, once it has run longer than the live-poll threshold.
The row carries no login. It is scored as an event of the user unknown: DBSheriff does not guess one, and a kill or a lock cannot target a login it does not have.

The server shows as monitored, and the Admin Console's Live Feed is quiet for the statements above. The producer logs a warning when a server starts on this source, and the posture scan reports the result as PS47 (statement capture insufficient), which also shows in the coverage page. Azure SQL Database is on this source unless you set up the database-scoped Extended Events session described in its section below, and that is the recommended setup there. With it the same DDL and DCL appear with the right login.

Automated by install_prereqs.py/setup_dbsheriff.py on on-prem for both items; AWS for Extended Events only; GCP for Extended Events only; neither on Azure SQL Database (both stay real, documented manual steps — see the Azure section).

install_prereqs.py walks both items on its own. setup_dbsheriff.py treats both as optional and applies them only when you name them with --optional-prereqs=extended_events,server_audit; without the flag it creates only the accounts, so Extended Events statement capture and the server audit stay off (and nothing warns you).

scripts/setup_dbsheriff.py mssql <hostname> --optional-prereqs=extended_events,server_audit   # choose your platform when asked

On-prem / self-managed

CREATE EVENT SESSION dbsheriff_stmt_file ON SERVER
 ADD EVENT sqlserver.sql_statement_completed(ACTION(sqlserver.server_principal_name, sqlserver.session_server_principal_name, sqlserver.session_id,
     sqlserver.client_hostname, sqlserver.client_app_name, sqlserver.database_name, sqlserver.query_hash, sqlserver.sql_text)
     WHERE sqlserver.is_system = 0 AND sqlserver.server_principal_name <> N'dbsheriff_classify' AND sqlserver.server_principal_name <> N'dbsheriff_kill' AND sqlserver.server_principal_name <> N'dbsheriff_lock' AND sqlserver.server_principal_name <> N'dbsheriff_monitor' AND sqlserver.server_principal_name <> N'dbsheriff_posture'),
 ADD EVENT sqlserver.sp_statement_completed(ACTION(sqlserver.server_principal_name, sqlserver.session_server_principal_name, sqlserver.session_id,
     sqlserver.client_hostname, sqlserver.client_app_name, sqlserver.database_name, sqlserver.query_hash, sqlserver.sql_text)
     WHERE sqlserver.is_system = 0 AND sqlserver.server_principal_name <> N'dbsheriff_classify' AND sqlserver.server_principal_name <> N'dbsheriff_kill' AND sqlserver.server_principal_name <> N'dbsheriff_lock' AND sqlserver.server_principal_name <> N'dbsheriff_monitor' AND sqlserver.server_principal_name <> N'dbsheriff_posture'),
 ADD EVENT sqlserver.error_reported(ACTION(sqlserver.server_principal_name, sqlserver.session_server_principal_name, sqlserver.session_id,
     sqlserver.client_hostname, sqlserver.client_app_name, sqlserver.database_name, sqlserver.sql_text)
     WHERE sqlserver.is_system = 0 AND sqlserver.server_principal_name <> N'dbsheriff_classify' AND sqlserver.server_principal_name <> N'dbsheriff_kill' AND sqlserver.server_principal_name <> N'dbsheriff_lock' AND sqlserver.server_principal_name <> N'dbsheriff_monitor' AND sqlserver.server_principal_name <> N'dbsheriff_posture' AND severity >= 14)
 ADD TARGET package0.event_file(SET filename = N'/var/opt/mssql/log/dbsheriff_stmt.xel', max_file_size = 64, max_rollover_files = 4)
 WITH (MAX_MEMORY = 4096 KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 2 SECONDS,
       MAX_EVENT_SIZE = 0 KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = ON);
ALTER EVENT SESSION dbsheriff_stmt_file ON SERVER STATE = START;

CREATE SERVER AUDIT dbsheriff_audit TO FILE (FILEPATH = '/var/opt/mssql/log/', MAXSIZE = 64 MB,
    MAX_ROLLOVER_FILES = 8, RESERVE_DISK_SPACE = OFF) WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);
CREATE SERVER AUDIT SPECIFICATION dbsheriff_server_spec FOR SERVER AUDIT dbsheriff_audit
  ADD (FAILED_LOGIN_GROUP), ADD (SUCCESSFUL_LOGIN_GROUP), ADD (LOGOUT_GROUP),
  ADD (SERVER_PRINCIPAL_CHANGE_GROUP), ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP), ADD (SERVER_PERMISSION_CHANGE_GROUP),
  ADD (AUDIT_CHANGE_GROUP), ADD (SERVER_OBJECT_CHANGE_GROUP), ADD (SERVER_STATE_CHANGE_GROUP), ADD (LOGIN_CHANGE_PASSWORD_GROUP),
  ADD (DATABASE_CHANGE_GROUP), ADD (DATABASE_PRINCIPAL_CHANGE_GROUP), ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
  ADD (DATABASE_PERMISSION_CHANGE_GROUP), ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP), ADD (SCHEMA_OBJECT_CHANGE_GROUP)
  WITH (STATE = ON);
ALTER SERVER AUDIT dbsheriff_audit WITH (STATE = ON);

Each event also records sqlserver.session_server_principal_name, the login that connected: server_principal_name is the principal in force and changes under EXECUTE AS, so without the second action a statement run while impersonating another login is recorded under that login, and DBSheriff will not lock it automatically (it cannot tell the impersonated login from the one that ran the statement: LOCK_SKIPPED, effective_role_ambiguous). A session created before this release lacks the action; scripts/install_prereqs.py mssql <host> replaces the events with ones that carry it (its --check reports the gap).

The session leaves DBSheriff's own logins out by the login SQL Server authenticated (sqlserver.server_principal_name <> N'…', one clause per login; an event predicate has no IN, so the conjunction is the NOT IN). The names above are the five logins this guide creates: if yours are named differently, put yours in every WHERE (scripts/install_prereqs.py mssql <host> --own-logins a,b,c writes the list for you). List only logins that exist and are DBSheriff's: a name the session excludes hides whoever later creates a login with that name. So the script writes a clause only for an own login that exists on the server (create the roles first; setup_dbsheriff.py does), says which names it left out, and its --check reports a session that excludes a name that is not a login as out of date. Edit the five names above in the same way if you skip an optional login (the posture login, for one) and run the item again when you create it later. Earlier versions of this guide excluded sessions by the client name DBSheriff instead. A client chooses that name itself, so a session that announced it emitted no event at all and none of its statements could be scored. A session created from the earlier text must be changed: run scripts/install_prereqs.py mssql <host> again (it detects the old predicate and replaces it), or by hand, per event, drop it and add it back with the new WHERE:

ALTER EVENT SESSION dbsheriff_stmt_file ON SERVER
 DROP EVENT sqlserver.sql_statement_completed, DROP EVENT sqlserver.sp_statement_completed, DROP EVENT sqlserver.error_reported,
 ADD EVENT sqlserver.sql_statement_completed(… WHERE sqlserver.is_system = 0 AND <the own-login clauses>), …;

If your edition refuses the combined form, the script falls back to the same change as two statements (the DROP EVENTs, then the ADD EVENTs), then to stopping the session first, then to DROP EVENT SESSION dbsheriff_stmt_file ON SERVER and the CREATE EVENT SESSION above again (the same name and file path); the session is started again whichever way it went.

The producer compares the live predicate with the registry's own logins every few minutes and logs a warning when they differ. DBSheriff's connections still announce APP=DBSheriff so that you can recognise them in sys.dm_exec_sessions; that name is for you and is never used to decide what is DBSheriff's.

sys.fn_get_audit_file needs CONTROL SERVER, which the monitor login must never hold — sign a reader procedure with a certificate instead so only the signed procedure carries the permission:

USE master;   -- the procedure and the certificate must be in master: DBSheriff calls master.dbo.dbsheriff_read_audit
GO
CREATE PROCEDURE dbo.dbsheriff_read_audit @path nvarchar(260), @since datetime2(3) = NULL AS
  SELECT event_time, sequence_number, action_id, succeeded, session_id, server_principal_name,
         database_principal_name, database_name, schema_name, object_name, client_ip, application_name,
         host_name, statement, additional_information, file_name, audit_file_offset, data_sensitivity_information
  FROM sys.fn_get_audit_file(@path, DEFAULT, DEFAULT) WHERE @since IS NULL OR event_time > @since
  ORDER BY event_time, sequence_number;
GO
CREATE CERTIFICATE dbsheriff_audit_cert ENCRYPTION BY PASSWORD = '<password>' WITH SUBJECT = 'DBSheriff audit reader';
ADD SIGNATURE TO dbo.dbsheriff_read_audit BY CERTIFICATE dbsheriff_audit_cert WITH PASSWORD = '<password>';
CREATE LOGIN dbsheriff_audit_cert_login FROM CERTIFICATE dbsheriff_audit_cert;
GRANT CONTROL SERVER TO dbsheriff_audit_cert_login;   -- effective only inside the signed procedure
GRANT EXECUTE ON dbo.dbsheriff_read_audit TO dbsheriff_monitor;
GRANT EXECUTE ON dbo.dbsheriff_read_audit TO dbsheriff_posture;   -- the same for the posture login, if you created it: the governance collector reads the audit with that login

Re-sign the procedure after any ALTER PROCEDURE — the signature is dropped, and DBSheriff reports it as monitor tamper.

Amazon RDS for SQL Server

scripts/setup_dbsheriff.py mssql <hostname>   # choose "aws"; asks for the RDS instance identifier

Extended Events: Standard and Enterprise only — the file target must be D:\rdsdbdata\log\dbsheriff_stmt.xel. On Express/Web, which rejects the CREATE outright, DBSheriff automatically falls back to the audit + dm_exec_query_stats below — nothing to configure for that.
Audit — never automated here, run it by hand. The CREATE/ALTER statements themselves would succeed, but the files stay unreadable until the instance's option group carries SQLSERVER_AUDIT (an IAM role with S3 access) — an AWS option-group change outside any database connection, so install_prereqs.py always raises guidance instead of guessing an IAM policy for you, even after you've set the option group up. Once you have, run the same CREATE SERVER AUDIT as the on-prem section above, except FILEPATH = 'D:\rdsdbdata\SQLAudit' and leave MAX_ROLLOVER_FILES at its default (RDS refuses any other value). The monitor then reads msdb.dbo.rds_fn_get_audit_file instead of the signed procedure above: sql CREATE USER dbsheriff_monitor FOR LOGIN dbsheriff_monitor; -- in msdb GRANT SELECT ON dbo.rds_fn_get_audit_file TO dbsheriff_monitor; -- the same for the posture login, if you created it: the governance collector reads the audit with that login CREATE USER dbsheriff_posture FOR LOGIN dbsheriff_posture; GRANT SELECT ON dbo.rds_fn_get_audit_file TO dbsheriff_posture;
Failed logins: rdsadmin.dbo.rds_read_error_log needs no grant — usable when the audit's login groups are off.
Kill: plain KILL — the master user's processadmin role covers it.
TLS: rds.force_ssl = 1 in the parameter group (static, reboot); register the server with ssl_mode = require, or verify-full with the AWS global CA bundle as MSSQL_SSL_CA.
Managed accounts (rdsa, NT SERVICE\…, NT AUTHORITY\…) are never findings, never lock targets, never scored.
Networking: same VPC, or publicly accessible with the DBSheriff host's address in the security group.

GCP Cloud SQL for SQL Server

scripts/setup_dbsheriff.py mssql <hostname>   # choose "gcp" when asked

TLS: register the server with ssl_mode = verify-ca (the default for Cloud SQL) and download the instance's server CA certificate (Cloud SQL, Connections, Security, "Manage server CA") to a file DBSheriff can read; set MSSQL_SSL_CA to that file. The server CA is a certificate authority, not the server's own certificate, so DBSheriff verifies the connection against it as a trust file instead of pinning it. For verify-full also set MSSQL_SSL_HOSTNAME to the name in the server certificate (Cloud SQL names it after the project and instance, not the IP address). If the connection is refused with a certificate error that these two settings do not resolve, register the server with ssl_mode = require: the connection is still encrypted, the certificate is not checked.

The built-in sqlserver admin's power is entirely through CustomerDbRootRole membership, not a direct grant — confirmed live: sys.server_permissions shows VIEW SERVER STATE/ALTER ANY LOGIN/etc as GRANT_WITH_GRANT_OPTION for that role, but standard SQL Server semantics don't let a role member re-grant a permission it only holds through membership. Every direct GRANT ... TO dbsheriff_* attempt for monitor/kill/lock/posture fails with "Grantor does not have GRANT permission" (4613) — the only working setup adds those four logins directly to CustomerDbRootRole, which makes them as privileged as the admin account itself. The scripts (setup_dbsheriff.py, onboard_target.py, create_reset_dbuser.py) will not make that blast-radius call unattended. They do the rest: each of the four logins is created with its generated password and a user in every user database, the account is counted as created and its credential is kept, and the one statement left is printed with the login's real name. Run it yourself, connected as the built-in sqlserver admin, once you've weighed it; until you do, the login connects but cannot read the server's sessions or end one, and DBSheriff reports permission errors for the server:

ALTER SERVER ROLE CustomerDbRootRole ADD MEMBER dbsheriff_monitor;
ALTER SERVER ROLE CustomerDbRootRole ADD MEMBER dbsheriff_kill;
ALTER SERVER ROLE CustomerDbRootRole ADD MEMBER dbsheriff_lock;
-- dbsheriff_posture: identical

Changing a password later. Once a login is a member, Cloud SQL refuses ALTER LOGIN … WITH PASSWORD for it (error 15151), so a reset (--reset-existing-accounts, or "reset" in create_reset_dbuser.py) drops the login and its database users, creates them again with the new password and adds the login back to CustomerDbRootRole — it was a member before, so that decision is already yours. A login that was never added gets its password changed in place and the statement above is printed again.

To do everything by hand instead, create the logins before adding them to the role (after that the password can only be changed by dropping and creating the login again), then the user in each database:

CREATE LOGIN dbsheriff_monitor  WITH PASSWORD = '<password>', CHECK_POLICY = ON, CHECK_EXPIRATION = OFF;
CREATE LOGIN dbsheriff_kill     WITH PASSWORD = '<password>', CHECK_POLICY = ON, CHECK_EXPIRATION = OFF;
CREATE LOGIN dbsheriff_lock     WITH PASSWORD = '<password>', CHECK_POLICY = ON, CHECK_EXPIRATION = OFF;
-- dbsheriff_posture: identical (same statement, same role membership)
-- in each database to monitor (without it the login is refused with error 4060 there):
USE <database>;
CREATE USER dbsheriff_monitor FOR LOGIN dbsheriff_monitor;
CREATE USER dbsheriff_kill    FOR LOGIN dbsheriff_kill;
CREATE USER dbsheriff_lock    FOR LOGIN dbsheriff_lock;
ALTER ROLE db_securityadmin ADD MEMBER dbsheriff_lock;
USE master;
ALTER SERVER ROLE CustomerDbRootRole ADD MEMBER dbsheriff_monitor;
ALTER SERVER ROLE CustomerDbRootRole ADD MEMBER dbsheriff_kill;
ALTER SERVER ROLE CustomerDbRootRole ADD MEMBER dbsheriff_lock;

dbsheriff_classify is unaffected and fully automated — its grants are per-database only, and sqlserver owns the database directly rather than through role membership. CREATE USER ... FOR LOGIN inside master is also blocked even after the role workaround, so the optional xp_readerrorlog fallback can't be set up on this platform either (low-impact — only used when the audit's login groups are off).

Extended Events: real per-statement capture works (a real VM under it), but the file path is fixed to exactly /var/opt/mssql/xevents/ (confirmed live via the server's own path rejection) — not /var/opt/mssql/log/. install_prereqs.py uses this path automatically.
Audit: not automatable — confirmed live: CREATE SERVER AUDIT fails with the same "Grantor does not have GRANT permission" (4613) even for the admin account itself, since CustomerDbRootRole membership doesn't extend to creating one. DBSheriff falls back to dm_exec_query_stats (no login attribution on completed statements) unless you set one up yourself, writing to the same fixed /var/opt/mssql/xevents/ directory.
Kill: plain KILL — covered by CustomerDbRootRole membership above, no separate grant.
Managed accounts (CloudDbSqlRoot/CloudDbSqlAgent, with an incrementing numeric suffix across maintenance) are never findings, never lock targets, never scored.
Database creation: Cloud SQL creates the target database via Terraform/console, not a CREATE DATABASE here — confirm it exists before running the per-database statements above.
Networking: Cloud SQL Auth Proxy, private IP peering, or an authorized public IP range covering the DBSheriff host.

Azure SQL Database

scripts/setup_dbsheriff.py mssql <hostname>   # choose "azure"; asks for the Azure server name + resource group

A PaaS product, not a real instance: the logical server has no server-level GRANT/REVOKE/DENY at all, and DMVs/permissions are scoped to the connected database only. Register one governance.monitored_servers row per database, not per logical server.

CREATE LOGIN never takes CHECK_POLICY/CHECK_EXPIRATION here (rejected outright, 42000/40517, confirmed live for every login — no local Windows security subsystem backs a PaaS login for either option to apply to). Every one of the five logins also needs ##MS_DatabaseConnector## membership just to connect at all — DBSheriff's first connection lands on the virtual master before it can detect Azure and reconnect to the real target, and fails outright (error 4064) without this:

-- connect to master as the server admin
CREATE LOGIN dbsheriff_monitor  WITH PASSWORD = '<password>';
CREATE LOGIN dbsheriff_kill     WITH PASSWORD = '<password>';
CREATE LOGIN dbsheriff_lock     WITH PASSWORD = '<password>';
CREATE LOGIN dbsheriff_classify WITH PASSWORD = '<password>';
-- dbsheriff_posture: identical to dbsheriff_monitor
ALTER SERVER ROLE ##MS_DatabaseConnector## ADD MEMBER dbsheriff_monitor;
ALTER SERVER ROLE ##MS_DatabaseConnector## ADD MEMBER dbsheriff_kill;
ALTER SERVER ROLE ##MS_DatabaseConnector## ADD MEMBER dbsheriff_lock;
ALTER SERVER ROLE ##MS_DatabaseConnector## ADD MEMBER dbsheriff_classify;
ALTER SERVER ROLE ##MS_ServerStateReader## ADD MEMBER dbsheriff_monitor;   -- monitor/posture only
ALTER SERVER ROLE ##MS_DefinitionReader##  ADD MEMBER dbsheriff_monitor;   -- monitor/posture only
ALTER SERVER ROLE ##MS_LoginManager##      ADD MEMBER dbsheriff_lock;      -- see "Lock", below

-- then, connected to each database to monitor (Azure SQL Database has no
-- cross-database USE — a fresh connection per database, not USE):
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'dbsheriff_monitor')  CREATE USER dbsheriff_monitor  FOR LOGIN dbsheriff_monitor;
GRANT VIEW DATABASE STATE TO dbsheriff_monitor;
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'dbsheriff_kill')     CREATE USER dbsheriff_kill     FOR LOGIN dbsheriff_kill;
GRANT VIEW DATABASE STATE TO dbsheriff_kill;
GRANT KILL DATABASE CONNECTION TO dbsheriff_kill;
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'dbsheriff_lock')     CREATE USER dbsheriff_lock     FOR LOGIN dbsheriff_lock;
GRANT CONTROL ON DATABASE::<database> TO dbsheriff_lock;   -- see "Lock", below
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'dbsheriff_classify') CREATE USER dbsheriff_classify FOR LOGIN dbsheriff_classify;
GRANT SELECT ON SCHEMA::dbo TO dbsheriff_classify;

Checking whether a login already exists (--check) also needs the per-database path here: sys.server_principals on the virtual master never reflects a customer-created login at all — confirmed live, even right after all five were created it still showed only the ten fixed ##MS_*## roles. The scripts handle this automatically; by hand, query sys.database_principals in an actual target database instead.

Extended Events — not automatable. No ON SERVER session exists on Azure SQL Database at all; the alternative is a database-scoped session writing to a blob container, which needs a database master key and a SAS-token credential this script won't generate blind. It's worth setting up by hand — without it, completed-statement attribution comes from dm_exec_query_stats alone, which live-tested at ~96.7% unknown: sql CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<a strong password>'; CREATE DATABASE SCOPED CREDENTIAL [https://<account>.blob.core.windows.net/<container>] WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = '<container SAS token, write+read+list, no leading ?>'; -- the credential's NAME must be the exact container URL — any other name -- fails with a misleading "operating system error 86: network password -- is not correct" (live-verified). CREATE EVENT SESSION dbsheriff_stmt_file ON DATABASE ADD EVENT sqlserver.sql_statement_completed( ACTION(sqlserver.session_id, sqlserver.username, sqlserver.client_hostname, sqlserver.client_app_name, sqlserver.database_name)) ADD TARGET package0.event_file( SET filename = 'https://<account>.blob.core.windows.net/<container>/dbsheriff_stmt_file.xel', max_file_size = 5, max_rollover_files = 3) -- keep small — see below WITH (MAX_MEMORY = 4MB, STARTUP_STATE = ON); ALTER EVENT SESSION dbsheriff_stmt_file ON DATABASE STATE = START; Enter the container URL, including its own SAS token (List+Read is enough: it is used only to discover the current file), in the Admin Console: Monitored Servers, edit the server, field XE blob URL. The console accepts only an https://<account>.blob.core.windows.net/<container>?...&sig=... address, stores it encrypted and never shows it again (the field says "set" when a value is stored; leave it blank to keep it, tick "clear XE blob URL" to remove it). The change history never records the value. The producer reads the same allow-list, so a value written into the database by hand that names another host is not called. Keep max_file_size small: there's no incremental/offset read for a blob-hosted file, so every read is a full read of the current file — a large file makes every read slow.
Audit — not automatable via T-SQL. CREATE SERVER AUDIT can't even be issued (Azure SQL Database doesn't support CREATE/ALTER/DROP of user objects in the virtual master at all) — Azure's native Auditing is configured through Azure Resource Manager instead, writing to a Storage-account blob container: bash az sql server audit-policy update --resource-group <rg> --name <server> \ --state Enabled --blob-storage-target-state Enabled \ --storage-account <storage-account> --retention-days 7 \ --actions FAILED_DATABASE_AUTHENTICATION_GROUP SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP \ DATABASE_LOGOUT_GROUP BATCH_COMPLETED_GROUP SCHEMA_OBJECT_CHANGE_GROUP DATABASE_OBJECT_CHANGE_GROUP \ DATABASE_PRINCIPAL_CHANGE_GROUP DATABASE_ROLE_MEMBER_CHANGE_GROUP DATABASE_PERMISSION_CHANGE_GROUP \ DATABASE_OBJECT_PERMISSION_CHANGE_GROUP SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP USER_CHANGE_PASSWORD_GROUP Omitting --blob-storage-target-state Enabled fails outright; a missing action group silently means missing coverage, not an error. Set mssql_audit_source = azure_blob and mssql_audit_path to the blob prefix (a trailing * silently matches nothing): https://<storage-account>.blob.core.windows.net/sqldbauditlogs/<ServerName>/master/ — every database's records land in one shared master subfolder since a July-2025 GA re-architecture, not one folder per database. Also grant dbsheriff_posture specifically (not just dbsheriff_monitor) GRANT VIEW DATABASE SECURITY AUDIT TO dbsheriff_posture; in the monitored database — the governance collector reads the audit with the posture credential.
Lock — read this before relying on it. ALTER LOGIN … DISABLE sets is_disabled = 1 in the catalog but does not actually block new connections on Azure SQL Database — confirmed live, even after flushing the auth cache and 2+ minutes elapsed, contradicting Microsoft's own documented behavior. The real lock is DENY CONNECT on the database user mapped to the login (GRANT CONNECT reverses it), which blocked new connections immediately and reliably in the same test — that's what GRANT CONTROL ON DATABASE::<database> above is for; ALTER LOGIN … DISABLE still runs too, for audit-trail consistency, but is never trusted as the lock itself.
Kill: plain KILL <session_id>, but the permission is KILL DATABASE CONNECTION (database-scoped) — ALTER ANY CONNECTION doesn't exist as a grantable concept here.
Auditing posture check (PS09/PS13) via Azure Resource Manager, not T-SQL — sys.server_audits doesn't exist on Azure SQL Database at all. Needs an Entra ID app registration granted the built-in SQL Security Manager role (read-only, no data-plane access), scoped to the SQL server resource, stored as JSON in the registry's posture_service_account_key_encrypted column: bash az ad sp create-for-rbac --name "dbsheriff-posture-<server>" --role "SQL Security Manager" \ --scopes "/subscriptions/<sub>/resourceGroups/<rg>/providers/Microsoft.Sql/servers/<server>"
Networking: the server firewall must allow the DBSheriff host's IP — there's no VPC/security-group model here.

Register the server

Admin Console → Monitored Servers → Add: engine sqlserver, host, port (1433), database, the credentials for whichever accounts you created, ssl_mode (require, or verify-full with MSSQL_SSL_CA). Switches: kill_mode, kill_scope (statement/session/login), burst_high_volume_threshold, scan_databases, mssql_statement_source (auto picks the XE file, then the audit, then dm_exec_query_stats), mssql_xe_session, mssql_audit_source (auto/audit_file/rdsaudit/ azure_blob/cloudwatch/none), mssql_audit_path, aws_region, rds_instance_id (these two name the RDS instance DBSheriff reads with its own cloud identity, so only an unrestricted approver sets or changes them), mssql_tag_writeback.

Verify

-- as dbsheriff_monitor
SELECT name FROM sys.dm_xe_sessions WHERE name = 'dbsheriff_stmt_file';   -- Extended Events running
SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1;
EXEC sys.xp_readerrorlog 0, 1, N'Login failed';                                                          -- on-prem: readable
EXEC master.dbo.dbsheriff_read_audit '/var/opt/mssql/log/dbsheriff_audit*.sqlaudit';                     -- on-prem: audit readable
SELECT COUNT(*) FROM msdb.dbo.rds_fn_get_audit_file('D:\rdsdbdata\SQLAudit\*.sqlaudit', default, default); -- RDS: audit readable
SELECT COUNT(*) FROM sys.fn_get_audit_file('https://<account>.blob.core.windows.net/sqldbauditlogs/<Server>/master/', default, default);  -- Azure: audit readable

DBSheriff connects with the monitor credential when it starts monitoring the server, within the registry reload interval (five minutes) of saving it; a credential the target refuses is logged by the producer as a refused monitor login and, if it keeps being refused, raises an availability finding. The other accounts are first used when their job runs (a kill, a lock, the posture and governance collection, a classification scan). Once it is monitoring, live data appears within a minute; the first posture/governance snapshot within the hour.

Connecting to the target securely

setup_dbsheriff.py, create_reset_dbuser.py, install_prereqs.py and onboard_target.py connect as your administrator, and the administrator password and every new account's password cross that connection, so they verify the server's certificate by default (--ssl-mode verify-full; --ca-file <bundle> names the CA, for SQL Server the server's certificate file, ODBC Driver 18's ServerCertificate; without it the operating system's trust store is used). A refused certificate stops the run and the message names the flag; --ssl-mode require encrypts without verifying (an explicit opt-down that prints a warning) and --ssl-mode disable --allow-plaintext sends everything in clear text (a trusted private link only). A generated password is never printed: it is stored in the registry (encrypted), or written to a new mode-600 file (--credentials-file) whose path is shown. Account names must match [A-Za-z][A-Za-z0-9_]*, and every database, schema or warehouse name the scripts discover is quoted as an identifier before it goes into a statement.

Optional: the Control account (native controls)

If you will let DBSheriff create standing native controls in this SQL Server instance (masks, limits, time-boxed grants) through the database's own features, or write the SQL for your DBA to run, read native controls privileges (inside the package) first: it says what each tier can do, what the optional Control account needs here, and how to remove everything DBSheriff made. The account is created only on request, with create_reset_dbuser.py mssql <host> --with-control; setup_dbsheriff.py never creates it, and native controls are off until an approver turns a server to script or managed.

Login	Can	Cannot
`dbsheriff_monitor`, `dbsheriff_posture`	`VIEW SERVER STATE`, `VIEW ANY DEFINITION`: read every session's current statement text and the catalogs	Read table rows, kill a session, change a login
`dbsheriff_kill`	`ALTER ANY CONNECTION`: end any session — a denial of service against every application login. It has a user in each database (so that its session can open the database) which holds no permission of its own	Read rows, change a login
`dbsheriff_lock`	`ALTER ANY LOGIN` (disable or re-password any login) and `ALTER ANY CONNECTION`, and `db_securityadmin` in every user database, where it can grant itself or anyone any permission on a table: data-access-equivalent. On Azure SQL Database `CONTROL` on each database	Act as `sysadmin` directly (it can, however, reach what a `sysadmin`-owned login can through the grants above)
`dbsheriff_classify`	`SELECT` on every row of every schema it was granted	Write, kill, change a login

The db_securityadmin membership is what the graduated response's DENY SELECT rung needs; if you do not use that rung you can drop the membership from the databases that do not need it. DBSheriff keeps these credentials encrypted in its registry (SERVER_CREDENTIALS_KEY); whoever can read both the registry and that key holds every login above. Create only the logins you will use (--roles).