How to give permission to execute stored proc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to give permission to execute stored proc

Author

Topic

azamsharp
Posting Yak Master

201 Posts

Posted - 2005-07-27 : 13:32:42

Hi,

I have a database server named "Server01". I have just added a new user "Azam001". Now I need to give this user permission to execute Stored procedurs existing in all the databases of this server. Is that possible or do I have to go to each database one by one and give this user permission to execute stored procedure.

Is there a script that I can run to give permission to a certain user to execute all sprocs.

Mohammad Azam
www.azamsharp.net

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-27 : 13:39:57

Yes you'll need to go to each database to grant permissions. I have a script called isp_Grant_Permissions that grants EXEC on all stored procedures that follow a certain naming standard (that part can easily be modified). Here it is:



CREATE PROC isp_Grant_Permissions
AS

SET NOCOUNT ON

DECLARE @objName sysname
DECLARE grant_perms_on_sps CURSOR FOR	
SELECT name
FROM SYSOBJECTS 
WHERE 	
	type = 'P' AND 
	[name] LIKE 'usp[_]%'  AND 
	uid = 1 AND
	status > -1

OPEN grant_perms_on_sps
FETCH NEXT FROM grant_perms_on_sps 
INTO @objName

WHILE @@FETCH_STATUS = 0
BEGIN
	EXEC ('GRANT EXECUTE ON dbo.' + @objName + ' TO RoleName')

	FETCH NEXT FROM grant_perms_on_sps 
	INTO @objName
END

CLOSE grant_perms_on_sps
DEALLOCATE grant_perms_on_sps
GO

Tara

azamsharp
Posting Yak Master

201 Posts

Posted - 2005-07-27 : 14:13:31

Hi,

THanks for the query. What is @obj_Name is that the name of the database. My stored procedures also start with usp_ so I guess thats good for me.

Thanks

Mohammad Azam
www.azamsharp.net

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-27 : 14:28:28

@objName is just used for the cursor to store the current object's name. Each trip around the loop changes this value, hence granting permissions to the objects. You just need to run the query in each of the databases. You could get rid of the stored procedure and just do this:


USE DB1
GO

SET NOCOUNT ON

DECLARE @objName sysname
DECLARE grant_perms_on_sps CURSOR FOR	
SELECT name
FROM SYSOBJECTS 
WHERE 	
	type = 'P' AND 
	[name] LIKE 'usp[_]%'  AND 
	uid = 1 AND
	status > -1

OPEN grant_perms_on_sps
FETCH NEXT FROM grant_perms_on_sps 
INTO @objName

WHILE @@FETCH_STATUS = 0
BEGIN
	EXEC ('GRANT EXECUTE ON dbo.' + @objName + ' TO RoleName')

	FETCH NEXT FROM grant_perms_on_sps 
	INTO @objName
END

CLOSE grant_perms_on_sps
DEALLOCATE grant_perms_on_sps

Hit F5 to execute, then change DB1 to your next database and hit F5 to execute again.

Tara

azamsharp
Posting Yak Master

201 Posts

Posted - 2005-07-27 : 14:32:38

Thanks got it :)

Mohammad Azam
www.azamsharp.net

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources