Grant Execute On SQL Objects

Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Published:
Updated:
I wrote this script few years ago and I still use it a lot. It's very useful when I receive scripts to run in databases and after I need to give permissions in created objects as Stored Procedures and Functions.

IMPORTANT:
- Don't forget to set value for @UserName
- Don't use this script if the user you want to give permissions shouldn't have execute permission on ALL objects.

------------------------------------ BEGIN ------------------------------

declare @ObjectName varchar(255)
declare @UserName varchar(255)
declare @GrantStmt nvarchar(4000)

set @UserName = 'DBUserName_Here'

declare Objects cursor for
select name from sysobjects
where (xtype = 'P' or xtype = 'FN')
  and name not like 'dt_%'

open Objects

FETCH NEXT FROM Objects INTO @ObjectName

WHILE @@FETCH_STATUS = 0
BEGIN
      set @GrantStmt = N'grant execute on ' + @ObjectName + ' to ' + @UserName
      EXEC SP_EXECUTESQL @GrantStmt
      FETCH NEXT FROM Objects INTO @ObjectName
END

close Objects
deallocate Objects

----------------------------------- END -----------------------------------
0
8,268 Views
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT

Comments (2)

CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
in 2005, you can do this:

grant exec on schema::dbo to username
It's actually better to create an executor role on the DB and then you can assign users that role as you ad them to the other roles


/* CREATE A NEW ROLE */
CREATE ROLE db_executor

/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor

In addition I often add AD security groups instead of users to SQL server.

That way, when I get a new user in for the Moomin Grooming department I just add them to the AD group. They get access to the network hares as well as the relevant DB permissions to required DBs.
You do nood to go into advanced when adding the DB user and tick groups to find them.

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.

Get access with a 7-day free trial.
You Belong in the World's Smartest IT Community