Assessing SQL Server Trust and Privilege Boundaries

SQL Server can sit between a user identity, a database, and the Windows host that runs the service. A sound assessment keeps these privilege layers distinct. Database access does not automatically mean operating system control, and a database role should not be treated as proof of access to every linked system.

Map the identity chain

For each in scope SQL Server instance, document the service identity, authentication mode, database roles, linked server relationships, and the business owner. Determine which Windows groups or service accounts can connect, administer the instance, or change stored configuration.

Review:

Read server role and linked server metadata

Run metadata queries with the designated assessment login. Start with the identity used for the connection, then review server role membership and linked server definitions. Do not enumerate application table contents unless the engagement explicitly requires it.

SELECT
    SUSER_SNAME() AS assessment_login,
    @@SERVERNAME AS server_name,
    SERVERPROPERTY('ProductVersion') AS product_version;

SELECT
    role_principal.name AS server_role,
    member_principal.name AS member_name,
    member_principal.type_desc AS member_type
FROM sys.server_role_members AS role_map
JOIN sys.server_principals AS role_principal
    ON role_map.role_principal_id = role_principal.principal_id
JOIN sys.server_principals AS member_principal
    ON role_map.member_principal_id = member_principal.principal_id
ORDER BY role_principal.name, member_principal.name;

SELECT name, product, provider, data_source, is_linked
FROM sys.servers
WHERE is_linked = 1
ORDER BY name;

Example output:

assessment_login              server_name           product_version
----------------------------- --------------------- ---------------
NORTHWIND\analyst01          SQL-APP-01            16.0.4105.2

server_role   member_name                member_type
-----------   -----------                -----------
sysadmin      NORTHWIND\DB Platform    WINDOWS_GROUP
securityadmin NORTHWIND\SQL Reviewers  WINDOWS_GROUP

name            product        provider       data_source   is_linked
----            -------        --------       -----------   ---------
ReportingLink   SQL Server     MSOLEDBSQL     BI-REPORT-01  1

Example names and reserved addresses are used. A linked server entry describes configuration; it does not establish which remote identity is used or what that identity can access. Ask the database owner to confirm the mapping and verify permissions with a benign query against an approved test endpoint.

Validate with test data

Start with configuration review and a designated test account. Use a test database or a client approved read only query to confirm the effective role. If a cross system relationship needs validation, use a harmless query against an approved test endpoint and record which identity the destination observed.

Inside a designated test database, inspect current database role membership without selecting customer data:

SELECT
    role_principal.name AS database_role,
    member_principal.name AS member_name,
    member_principal.type_desc AS member_type
FROM sys.database_role_members AS role_map
JOIN sys.database_principals AS role_principal
    ON role_map.role_principal_id = role_principal.principal_id
JOIN sys.database_principals AS member_principal
    ON role_map.member_principal_id = member_principal.principal_id
ORDER BY role_principal.name, member_principal.name;

Capture the database name, login, role, time, and query result. If the client needs a permission test, ask the database owner to create a non sensitive marker table in a test database and approve a specific SELECT query. Do not use xp_cmdshell, enable external execution features, or change linked server mappings to turn a role review into host execution.

Do not enable external execution features, alter server wide settings, create a linked server, or run operating system commands during a routine assessment. These actions can affect service availability and may grant access beyond the original scope. If a high impact validation is required, agree on a maintenance window, test data, rollback steps, and a system owner before proceeding.

Defensive improvements

Remove unused linked servers and logins. Restrict server administrator roles, separate database administration from Windows administration, and use dedicated managed service identities where supported. Disable features that are not needed, audit changes to server configuration, and periodically verify that service accounts have only the rights required by the application.

Further reading

• Microsoft Learn: SQL Server security best practices