SQL DCL Scripting for Automation

• GRANT allows, DENY explicitly blocks and REVOKE removes an explicit decision.
• Assign policy to roles and membership to users.
• Give Runtime EXECUTE on controlled write procedures instead of unrestricted table writes.
• Test effective permissions as the target user before deployment.

1. What Is DCL?

DCL means Data Control Language. It manages who can perform an action on a SQL Server securable.

CREATE USER, CREATE ROLE and ALTER ROLE prepare security principals and memberships; they are security-management DDL used alongside DCL.

2. Understand the Authorization Model

A permission decision connects three elements:

1. Principal: who requests access—login, database user or role.
2. Securable: what is protected—server, database, schema, table, view or procedure.
3. Permission: which action—SELECT, INSERT, UPDATE, DELETE, EXECUTE or another permission.

GRANT EXECUTE
ON OBJECT::[dbo].[usp_RecordEvent]
TO [scada_runtime_role];

Here the principal is scada_runtime_role, the securable is the procedure and the permission is EXECUTE.

3. Login, Database User and Role

For SQL Server, a login is normally mapped to a user in each required database. For the safe lab, use a contained test user without a login:

USE [SQF_DB];
GO

CREATE USER [scada_lab_user] WITHOUT LOGIN;
GO

4. Securables and Permission Scope

Permissions form a hierarchy. A grant at a broad scope can flow to contained securables.

Start at object scope. Move to schema scope only when every current and future object in that schema is intended for the same audience.

5. GRANT: Allow an Approved Action

GRANT SELECT
ON OBJECT::[dbo].[vw_ChargeSummary]
TO [scada_runtime_role];

GRANT EXECUTE
ON OBJECT::[dbo].[usp_RecordEvent]
TO [scada_runtime_role];

Use explicit securable classes such as OBJECT:: and SCHEMA:: in deployment scripts. They make the intended permission scope easier to review.

6. DENY: Explicitly Block a Permission

DENY DELETE
ON OBJECT::[dbo].[tblEvent]
TO [scada_runtime_role];

DENY is useful when a role inherits a broader grant that must be blocked. However, no grant is simpler than a deny when you control the full role design.

Do not casually deny permissions to public; every database user belongs to that role and the effect can be much wider than intended.

7. REVOKE: Remove an Explicit Decision

-- Remove an explicit GRANT or DENY at this scope.
REVOKE DELETE
ON OBJECT::[dbo].[tblEvent]
FROM [scada_runtime_role];

REVOKE does not mean “block”. After revocation, the user may still receive the permission through another role or a broader grant. Recalculate effective permissions after every change.

8. How SQL Server Calculates Effective Permissions

Effective access can come from the user, one or more roles, broader permission scopes, ownership or execution context. In general, an applicable DENY overrides a GRANT, subject to documented exceptions.

SELECT
    HAS_PERMS_BY_NAME(N'dbo.vw_ChargeSummary', N'OBJECT', N'SELECT') AS CanReadSummary,
    HAS_PERMS_BY_NAME(N'dbo.usp_RecordEvent', N'OBJECT', N'EXECUTE') AS CanRecordEvent,
    HAS_PERMS_BY_NAME(N'dbo.tblEvent', N'OBJECT', N'DELETE') AS CanDeleteEvents;

Run this under the target execution context. A result of 1 means permission, 0 means no permission and NULL can indicate an invalid securable/permission context.

9. Create a Custom SCADA Role

CREATE ROLE [scada_runtime_role] AUTHORIZATION [dbo];
GO

ALTER ROLE [scada_runtime_role]
ADD MEMBER [scada_lab_user];
GO

Roles express job functions: scada_runtime_role, report_reader_role, maintenance_engineer_role and deployment_role. Avoid creating slightly different permissions directly on dozens of individual users.

-- Remove membership during offboarding or role change.
ALTER ROLE [scada_runtime_role]
DROP MEMBER [scada_lab_user];

10. Design Read-Only Reporting Access

Expose approved columns through a view and grant access to the view rather than every base table.

CREATE ROLE [report_reader_role] AUTHORIZATION [dbo];

GRANT SELECT
ON OBJECT::[dbo].[vw_ChargeSummary]
TO [report_reader_role];

When the view and referenced objects share an owner, ownership chaining can allow view access without separate base-table permissions. Dynamic SQL and cross-database access can break that simple chain, so test the exact module.

11. Design Execute-Only Runtime Writes

Place validation and DML inside a stored procedure, then grant only EXECUTE.

GRANT EXECUTE
ON OBJECT::[dbo].[usp_RecordEvent]
TO [scada_runtime_role];

-- No direct INSERT/UPDATE/DELETE grants are required for a normal ownership chain.

This design limits the write shape, validates equipment and types, returns the created identity and lets the database owner change table internals without granting Runtime broader access.

12. Use Schema-Level Grants Deliberately

CREATE SCHEMA [reporting] AUTHORIZATION [dbo];
GO

GRANT SELECT
ON SCHEMA::[reporting]
TO [report_reader_role];

A schema-level grant can simplify administration for a governed reporting schema. It also applies to appropriate future objects added to that schema. Do not use GRANT CONTROL ON SCHEMA merely to provide read access.

13. Why Broad Fixed Roles Are Risky

Custom roles make the access matrix visible. Fixed roles can be appropriate for DBA operations, but not as a shortcut for normal Runtime connectivity.

14. WITH GRANT OPTION

-- Powerful delegation; normally not for Runtime identities.
GRANT SELECT
ON OBJECT::[dbo].[vw_ChargeSummary]
TO [report_security_admin]
WITH GRANT OPTION;

WITH GRANT OPTION lets the grantee grant the same permission to others. Restrict it to controlled security administrators. Revoking a permission that was delegated can require CASCADE; understand the downstream grants before changing it.

15. Test with EXECUTE AS and REVERT

EXECUTE AS USER = N'scada_lab_user';

SELECT USER_NAME() AS [ExecutionUser];
SELECT
    HAS_PERMS_BY_NAME(N'dbo.vw_ChargeSummary', N'OBJECT', N'SELECT') AS CanReadSummary,
    HAS_PERMS_BY_NAME(N'dbo.usp_RecordEvent', N'OBJECT', N'EXECUTE') AS CanRecordEvent,
    HAS_PERMS_BY_NAME(N'dbo.tblEvent', N'OBJECT', N'DELETE') AS CanDeleteEvents;

REVERT;
SELECT USER_NAME() AS [RestoredUser];

16. Audit Roles and Explicit Permissions

SELECT
    rolep.[name] AS [RoleName], memberp.[name] AS [MemberName]
FROM sys.database_role_members AS drm
JOIN sys.database_principals AS rolep
    ON rolep.[principal_id] = drm.[role_principal_id]
JOIN sys.database_principals AS memberp
    ON memberp.[principal_id] = drm.[member_principal_id]
ORDER BY rolep.[name], memberp.[name];

SELECT
    grantee.[name] AS [PrincipalName],
    p.[state_desc], p.[permission_name], p.[class_desc],
    CASE
        WHEN p.[class] = 1 THEN OBJECT_SCHEMA_NAME(p.[major_id])
        WHEN p.[class] = 3 THEN SCHEMA_NAME(p.[major_id])
    END AS [SecurableSchema],
    CASE
        WHEN p.[class] = 1 THEN OBJECT_NAME(p.[major_id])
        WHEN p.[class] = 3 THEN N'(schema)'
    END AS [SecurableName]
FROM sys.database_permissions AS p
JOIN sys.database_principals AS grantee
    ON grantee.[principal_id] = p.[grantee_principal_id]
WHERE grantee.[name] IN (N'scada_runtime_role', N'report_reader_role')
ORDER BY grantee.[name], p.[class_desc], p.[permission_name];

Catalog views show explicit permissions, not a complete flattened result of every inheritance path or fixed-role capability. Combine catalog review with impersonation and HAS_PERMS_BY_NAME.

17. Protect Automation Credentials

• Prefer Windows or managed identities and dedicated service accounts.
• Do not reuse personal DBA accounts for Runtime.
• Store secrets in an approved secret store, not in HTML, VBS, Python source or HMI text fields.
• Use encrypted connections and validate the SQL Server certificate.
• Rotate credentials under a tested procedure that avoids production downtime.
• Disable or remove accounts immediately during decommissioning.
• Log authentication failures and permission changes without exposing passwords.

18. Separate Runtime, Reporting and Deployment

This separation limits blast radius and creates a clearer audit trail.

19. Common DCL Errors and Fixes

CREATE USER [scada_lab_user] WITHOUT LOGIN;
CREATE ROLE [scada_runtime_role] AUTHORIZATION [dbo];
ALTER ROLE [scada_runtime_role] ADD MEMBER [scada_lab_user];

GRANT SELECT ON OBJECT::[dbo].[vw_ChargeSummary] TO [scada_runtime_role];
GRANT EXECUTE ON OBJECT::[dbo].[usp_RecordEvent] TO [scada_runtime_role];

REVOKE SELECT ON OBJECT::[dbo].[vw_ChargeSummary] FROM [scada_runtime_role];
-- Retest as scada_lab_user: CanReadSummary should now be 0.
GRANT SELECT ON OBJECT::[dbo].[vw_ChargeSummary] TO [scada_runtime_role];