# SQL Server Permissions

![](/files/bV66cgZUHAhsDBXbIqYV)

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


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://learn.capstorm.com/cs-govern/best-practices/sql-server-permissions.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.