SQL Server Permissions

SQL Server Permissions

Ensure that CopyStorm is connected to a Microsoft SQL Server database. If this is the initial setup, follow the recommended best practices for configuring CopyStorm with SQL Server.

Required Permissions

Permission Required	Why It’s Needed
CREATE TABLE	Required to create Govern tables during initial installation.
ALTER ON SCHEMA::[schema]	Allows creation and modification of objects (like tables, indexes, and functions) in the Govern schema.
INSERT	Used to seed initial data such as Govern access categories and configuration values.
UPDATE	Allows updates to seed/config rows and ongoing modifications to Govern metadata.
DELETE	Used to remove existing config values, masking rules, or other records during reinitialization or management.
SELECT	Required to query Govern tables (e.g., metadata, access categories, encrypted values).
REFERENCES	Needed to define foreign key relationships between Govern tables (e.g., rule → field mappings).
CREATE FUNCTION	Required to define UDFs such as guard_encrypt and guard_archive_get.
CREATE INDEX	Allows creation of indexes to optimize performance on frequently queried columns.
EXECUTE ON [schema].guard_archive_get	Grants access to the main Govern UDF for retrieving decrypted field values, if the user has access. Internal function calls are executed under owner context.
SET IDENTITY_INSERT	Required to insert predefined identity values into tables like GovernAccessCategory.
CONTROL SERVER (optional)	Only required if using OLE Automation procedures (e.g., for developer-mode logging inside encryption UDFs).

Check Database Level Permissions

Database-level permissions include actions like creating tables, procedures, functions, and managing schemas or indexes.

-- Check effective database-level permissions for current user
SELECT * 
FROM fn_my_permissions(NULL, 'DATABASE') 
WHERE permission_name IN (
    'ALTER ANY SCHEMA',
    'CREATE FUNCTION',
    'CREATE PROCEDURE',
    'CREATE TRIGGER',
    'CREATE TABLE',
    'CREATE INDEX',
    'VIEW DEFINITION',
    'CONTROL',
    'ALTER',
    'VIEW ANY DEFINITION'
);

Expected Output

This output reflects the effective database-level permissions for the current user; results may vary depending on your specific permissions, roles, and inherited access rights.

permission_name	class_desc
CREATE PROCEDURE	DATABASE
CREATE TABLE	DATABASE
CREATE INDEX	DATABASE
VIEW DEFINITION	DATABASE
ALTER	DATABASE

Check Schema Level Permissions

The below SQL is useful for analyzing available schema-level permissions to the SQL Server user:

-- Check permissions on specific schema (e.g., dbo)
SELECT * 
FROM fn_my_permissions('dbo', 'SCHEMA') 
WHERE permission_name IN (
    'ALTER',
    'CONTROL'
);

Expected Output

This output reflects the effective schema-level permissions for the current user; results may vary depending on your specific permissions, roles, and inherited access rights. If ALTER or CONTROL is returned, the user has sufficient rights to create DML triggers on any object within the specified schema.

permission_name	class_desc
ALTER	SCHEMA