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

# Stored Procedure to assign permissions
- URL: https://blog.tsd.digital/stored-procedure-to-assign-permissions/
- Published: 2006-10-09T13:42:49.000Z
- Updated: 2006-10-09T13:42:49.000Z
- Author: Tim Gaunt
- Tags: SQL Server, #Import 2025-04-01 05:34

This is a useful stored procedure for assigning permissions to users quickly and easily. We tend to assign a new login to each application we develop, this way we limit the damage possible in the event of a username/password compromise.

/\*-------------------------------------------------------------------------- Automatically assign the role permissions --------------------------------------------------------------------------\*/USE DatabaseNameSET NOCOUNT ONDECLARE @objName varchar(80)DECLARE @objType char(2)DECLARE @username varchar(100)SET @username = 'UserNameToAssignPermissionsTo'DECLARE grant\_perms\_on\_sps CURSOR FORSELECT name, typeFROM SYSOBJECTS WHERE ( (type = 'P')OR (type = 'FN')OR (type = 'TF')OR (type = 'U')OR (type = 'V') )AND uid = 1AND status > -1ANDLEFT(name, 3) <> 'dt\_'--See Note 1OPEN grant\_perms\_on\_spsFETCH NEXT FROM grant\_perms\_on\_sps INTO @objName, @objTypeWHILE@@FETCH\_STATUS = 0BEGIN IF @objType = 'P'OR @objType = 'FN'BEGIN EXEC ('GRANT EXECUTE ON dbo.' + @objName + ' TO ' + @username) PRINT ('GRANTED EXECUTE ON dbo.' + @objName + ' TO ' + @username) END IF @objType = 'TF' BEGIN EXEC ('GRANT SELECT ON dbo.' + @objName + ' TO ' + @username) PRINT ('GRANTED SELECT ON dbo.' + @objName + ' TO ' + @username) END FETCH NEXT FROM grant\_perms\_on\_sps INTO @objName, @objTypeENDCLOSE grant\_perms\_on\_spsDEALLOCATE grant\_perms\_on\_spsGO------------------------------------------------------------------------

**Note 1:** In addition, we tend to prefix our database objects with useful prefixes to group relevant tables, i.e. if we had login information stored in the database we may use “Login\_” as the prefix, using this method with this Stored Procedure to assign permissions you can easily select the relevant objects. So you could alter the stored procedure a touch:

DECLARE @prefix varchar(100)SET @prefix = 'PrefixToUse'LEFT(name, LEN(@prefix)) = @prefix