devxlogo

Grant SQL Execute Permissions to a User

Grant SQL Execute Permissions to a User

Oftentimes, when I’m creating stored procedures, I forget to grant execute permissions to my user(s). Here is a stored procedure that will do this for you by simply passing in the username or a comma delimited list of users. Want to revoke permissions? Replace GRANT with REVOKE!

CREATE PROCEDURE GrantExecuteForUser(@user varchar(30))ASdeclare @sql varchar(255)declare grant_cur cursor forselect 'grant execute on ' + name + ' to ' + @user as sqlfrom sysobjectswhere xtype = 'P' AND	name not like 'dt_%'order by nameopen grant_curFETCH NEXT FROM grant_cur INTO @sqlWHILE (@@fetch_status  -1) BEGIN	exec(@sql)	FETCH NEXT FROM grant_cur INTO @sqlENDclose grant_curdeallocate grant_curgo

devx-admin

Share the Post: