Execute Permissions in StoredProc 1

[crazyboybert]

Hi All

Does anyone know how to change the default execute permissions for Stored Procedures? When I create a procedure through Enterprise manager no execute permissions are granted on a stored procedure when it is created for any SQL accounts. Once the procedure is created then it needs to be reopened to set execute permissions for 'public' or the appropriate SQL account.

This in itself is a bit of a bother but I can live with it. More annoying is when using VisualStudio.NET to create procedures no execute permissions can be set at all. Therefore, I still need to use Enterprise Manager or Query Analyser to grant permissions on the procedure. This is a phaff I could really do without and to some extent invalidates the use of VS.NET for creating and editing stored procs....

What I would really like is execute permissions set by default on a database by database basis...Any help much appreciated.

Rob

Go placidly amidst the noise and haste, and remember what peace there may be in silence - Erhmann 1927

 

[johnherman]

As far as I know, that's the way security for SP's works, and has worked that way since 6.5.

You could perhaps write a SQL Script that inquires all sp's in sysobjects and grants public (or a designated account, or both) to each one in turn.

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[crazyboybert]

Thanks johnherman

Not quite what i'm looking for though. In a development situation it would be nice to create my stored procedures in Visual Studio and then be able to run a web app using them straight away without having to use an SQL admin tool as well to change the permissions...Otherwise I may as well go back to using Enterprise Manager to write my procedures....

Rob

Go placidly amidst the noise and haste, and remember what peace there may be in silence - Erhmann 1927

 

[dragonwell]

This might seem a litle half-baked, but here's what I do: add the MACHINENAME\ASPNET account as a User at the server level, and make it DBO. Then since he'll be dbo for all new objects you add to the database, he'll automatically have execute permissions.

 

[crazyboybert]

Thanks Dragonwell. You have given me the idea that has solved my problem. Eaqch of our internal applications have a domain user account they impersonate to provide functionality such as access to AD a file permissions on network drives. Add these accounts a dbo on the required database and bingo. Problem solved.

Cheers


Rob

Go placidly amidst the noise and haste, and remember what peace there may be in silence - Erhmann 1927