> For the complete documentation index, see [llms.txt](https://learn.capstorm.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://learn.capstorm.com/cs-govern/best-practices/sql-server-permissions.md).

# SQL Server Permissions

![](https://869259762-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FyE2QoOSmKpot88Q3kHGR%2Fuploads%2Fgit-blob-3236c0be7548c6fa1a53dd74f8584140f5e6ccbb%2F5fcf6d03-Govern-Banner.png?alt=media)

## 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](https://learn.capstorm.com/copystorm/readme/best-practices/microsoft-sql-server-database-setup).

## 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      |