<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Grant Execute On SQL Objects

Published on
10,966 Points
7,966 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

Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Join & Write a Comment

Via a live example, show how to shrink a transaction log file down to a reasonable size.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month