SQL Server Object Permissions: Generate GRANT Statements

calendar Aug 8, 2025 / · 7 min read · sql server database security permissions grant statements database administration sql scripts security database objects sysobjects sys.objects database roles sys.database_permissions sys.database_principals  ·
Share on: linkedin copy

Giving an application role read, write and execute rights on a database usually means one GRANT per table per permission plus one per stored procedure, which runs to hundreds of statements on a mid-sized schema. The script below writes those statements for you from sys.objects, schema-qualified and bracket-quoted, so they can be reviewed and then run as one batch.

Purpose and Overview

Setting up a new application role is the usual job-to-be-done here: a database role such as SelectInsertUpdateDeleteExecSP needs SELECT, INSERT, UPDATE and DELETE on the user tables and EXECUTE on the stored procedures, and nobody wants to type those by hand. Because the script only builds text, nothing changes until you copy its output into a query window and execute it, which makes it safe to run on production to see what it would do.

The older form of this script, still common on DBA blogs, read the legacy sysobjects view and concatenated the bare object name. That version has three gaps. Microsoft describes sys.sysobjects as a SQL Server 2000 system table kept as a view for backward compatibility and says the feature will be removed in a future version. Bare names carry no schema, so a table in any schema other than the caller's default resolves to the wrong object or fails. And names with spaces or reserved words break the generated statement. The version below reads sys.objects, adds the schema with SCHEMA_NAME, and wraps every identifier in QUOTENAME.

The output is one column, grant_statement, with one row per object per permission, sorted so the statements for each object sit together.

Code Breakdown

The whole script is one query with two branches, tables and procedures, joined by UNION ALL.

 1DECLARE @role sysname = N'SelectInsertUpdateDeleteExecSP';
 2
 3SELECT 'GRANT ' + p.permission_name + ' ON '
 4       + QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.' + QUOTENAME(o.name)
 5       + ' TO ' + QUOTENAME(@role) + ';' AS grant_statement
 6FROM sys.objects AS o
 7CROSS JOIN (VALUES ('SELECT'), ('INSERT'), ('UPDATE'), ('DELETE')) AS p(permission_name)
 8WHERE o.type = 'U'
 9  AND o.is_ms_shipped = 0
10UNION ALL
11SELECT 'GRANT EXECUTE ON '
12       + QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.' + QUOTENAME(o.name)
13       + ' TO ' + QUOTENAME(@role) + ';'
14FROM sys.objects AS o
15WHERE o.type IN ('P', 'PC')
16  AND o.is_ms_shipped = 0
17ORDER BY grant_statement;

The role variable

@role is declared as sysname, the data type SQL Server uses for identifiers, and is passed through QUOTENAME like every object name. Change the one value to point the whole output at a different role. The role itself must already exist; create it first with CREATE ROLE.

The table branch

sys.objects holds one row per schema-scoped object. Its type column is a short code, and Microsoft's reference lists U = Table (user-defined). The CROSS JOIN against a four-row VALUES list multiplies each table into four rows, one per permission, so a database with 200 tables returns 800 table statements. is_ms_shipped = 0 drops objects that the reference describes as "created by an internal SQL Server component".

The procedure branch

The second SELECT emits GRANT EXECUTE for type P (SQL stored procedure) and PC (assembly, or CLR, stored procedure). The legacy script filtered on 'p' alone, which skipped CLR procedures entirely.

Schema and quoting

SCHEMA_NAME(o.schema_id) turns the schema ID into its name, and QUOTENAME returns a delimited identifier with square brackets. That is what makes the output safe for a table called Order Details or a schema called user. One documented limit applies: QUOTENAME accepts up to 128 characters and returns NULL for longer input. Object names are sysname, which is 128 characters, so a real object name never reaches that limit.

Sorting

ORDER BY grant_statement sorts on the alias from the first branch, which is how a UNION ALL result is ordered. Sorting the text keeps each object's four table grants together and makes the output easy to compare between environments.

Checking what the role already holds

Before running the output, it is worth listing what the role has today, so the new grants can be compared with the old ones. sys.database_permissions returns a row for every permission in the database; joining it to sys.database_principals and sys.objects names the role, the object and the state.

1SELECT QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.' + QUOTENAME(o.name) AS object_name,
2       pe.permission_name,
3       pe.state_desc
4FROM sys.database_permissions AS pe
5INNER JOIN sys.database_principals AS pr ON pe.grantee_principal_id = pr.principal_id
6INNER JOIN sys.objects AS o ON pe.major_id = o.object_id
7WHERE pe.class = 1
8  AND pr.name = N'SelectInsertUpdateDeleteExecSP'
9ORDER BY object_name, pe.permission_name;

class = 1 limits the rows to object and column permissions, and state_desc shows GRANT, DENY or GRANT_WITH_GRANT_OPTION. A DENY row matters: Microsoft documents that DENY takes precedence over all permissions, except for object owners and members of sysadmin, so a member of a role with a deny on an object will not get access from the generated GRANT until the deny is removed.

Key Benefits and Use Cases

  • New application role setup — one run produces the full grant list for a role that needs CRUD on tables and execute on procedures.
  • Review before change — the script writes statements and executes nothing, so the output can be read, trimmed and approved before it runs.
  • Environment parity — run it against development, test and production and diff the output to spot objects that exist in one and not another.
  • Schema-correct output — every statement names its schema, so objects outside dbo get the grant they need.
  • Rerunnable after releases — new tables and procedures appear the next time it runs; the output then needs executing again.

Performance Considerations

  • Cheap to run — it reads catalog metadata only, never user data, so it returns quickly even on large schemas.
  • Output size grows fast — four rows per table plus one per procedure; on a database with thousands of tables, save the result to a file rather than reading it in the grid.
  • Executing the output is a series of metadata changes — each GRANT is small, but on a busy system run a long list outside peak hours.
  • Views and functions are not covered — the filter only takes tables and procedures. Add 'V' to the table branch for views, or 'FN', 'IF' and 'TF' for functions, if the role needs them.

Practical Tips

  • Grant at the schema level when the role should see everything — GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON SCHEMA::dbo TO SelectInsertUpdateDeleteExecSP; replaces the per-object list with one statement per schema.
  • Check the fixed roles first — Microsoft documents that db_datareader members "can read all data from all user tables and views" and db_datawriter members "can add, delete, or change data in all user tables". If those cover the need, there is nothing to generate.
  • Trim before running — remove DELETE for roles that only need to insert and update, or drop audit tables from the list, rather than granting everything and revoking later.
  • Make sure you can grant — Microsoft requires the grantor to hold the permission with GRANT OPTION, or a higher permission that implies it.
  • Keep the output with the release — saving the generated statements beside the deployment script records exactly what was granted and when.

Conclusion

This script turns a role's permission set into a reviewable list of GRANT statements built from sys.objects, with schema and quoting handled. Use it when a role needs object-by-object rights, and reach for a schema-level grant or a fixed database role when it needs everything.

References

Posts in this series