Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL
Expertise: Advanced
Mar 9, 2004

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))
AS
declare @sql varchar(255)
declare grant_cur cursor for
select 'grant execute on ' + name + ' to ' + @user as sql
from sysobjects
where xtype = 'P' AND
	name not like 'dt_%'
order by name

open grant_cur
FETCH NEXT FROM grant_cur INTO @sql
WHILE (@@fetch_status <> -1) BEGIN
	exec(@sql)
	FETCH NEXT FROM grant_cur INTO @sql
END
close grant_cur
deallocate grant_cur
go
Ian Lane
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date