> ## Content Index
> Fetch the complete content index at: https://blog.tsd.digital/llms.txt
> Use this file to discover other available public pages before exploring further.

# Bulk Granting EXECTUTE Permission on SQL Server
- URL: https://blog.tsd.digital/bulk-granting-exectute-permission-on-sql-server/
- Published: 2006-07-03T13:42:17.000Z
- Updated: 2006-07-03T13:42:17.000Z
- Author: Tim Gaunt
- Tags: SQL Server, #Import 2025-04-01 05:34

Here's some code I regularly find useful when going from a dev server to a production server (or changing the username), I'm sure there's a more automated way but for a quick fix...

DECLARE @username varchar(100)SET @username = 'xyz'SELECT'GRANT EXECUTE ON ' + name + ' TO ' + @username FROMsysobjectsWHERE xtype = 'p'ANDLEFT(name, 4) = 'PRE\_'

The code is simple, it lists all the stored procedures with a set prefix and generates the T-SQL to grant EXECUTE permissions to a given user. 

To use it, set the username, update the last line if you would like to limit it's bounds (we prefix ours for clarity i.e. SProcs with a prefix of CMS\_ are used in the Content Management System) and run it, then copy the results and run them in a Query Analyser.