<

Grant Execute On SQL Objects

Published on
10,872 Points
7,872 Views
Last Modified:
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
Comment
2 Comments
 
LVL 60

Expert Comment

by:chapmandew
in 2005, you can do this:

grant exec on schema::dbo to username
0
 
LVL 10

Expert Comment

by:Bodestone
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.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Join & Write a Comment

Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month