sp drop / create with permission

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 2012 Forums

Transact-SQL (2012)

sp drop / create with permission

Author

Topic

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2014-09-23 : 10:19:23

Hello experts,

Something really bugging me about a script I'm trying to do. I want my script to be rerunnable at anytime so I have a check for existing, then drop, then re-create.

The problem here I think is something to do with my return position. If I run this as is, I get a permission error from my app. If I manually run the last line, which grants the permission my app is the fine. But any subsequent full runs of the whole script bring the issue back.

I'm missing something. Perhaps another begin / end? Any help would be immensely appreciated. Thanks for your time;



IF EXISTS (SELECT 1 FROM sys.objects WHERE name = 'mySp' AND type = 'P' )
	BEGIN 
		DROP PROCEDURE dbo.mySp
	END 
GO

CREATE PROCEDURE mySp
@input VARCHAR(150),
@return VARCHAR(50) OUT -- returns 1 for ok, 0 for bad (not found / registered) 
AS
BEGIN
IF EXISTS (SELECT 1 FROM table WHERE condition = @input) 
		BEGIN 
			SET @return = 0 --bad
			RETURN
		END 
	ELSE 
		-- DO SOME OTHER STUFF
RETURN
END
GRANT EXEC ON dbo.mySp TO User

"Impossible is Nothing"

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2014-09-23 : 10:47:54

Fixed. I needed a GO after my RETURN END.

So

 
           ELSE 
		-- DO SOME OTHER STUFF
RETURN
END
GO <!----- this was missing
GRANT EXEC ON dbo.mySp TO User

Thank you for your time! :)

edit: added thanks.

"Impossible is Nothing"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources