Re: Set perissions on stored procedure

Andy (andys@xxxxxxxxxxxxxx) writes:
Hi there in Enterprise Manager I could grant execute permissions on a
stored procedure for one of my database roles, by right clicking the
stored procedure and choosing All Tasks -> Manage Permissions.

Is there any way I can do this in Management Studio Express for a SQL
Server 2005 Express database? I know I could run a bit of T-SQL along
the lines of

GRANT EXECUTE ON [dbo].[spMyStoredProcedure] TO [MyDatabaseRole]

but it would be nice to be able to do it in Management Studio.

Right-click the procedure in Object Explorer and select Properties. Look
to the left to find Permissions.

Note: I'm using SSMS from Developer Edition, but I would expect this
be possible in Mgmt Studio Express as well.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: Deny access to all users (including Administrator and DomainAd
    ... permissions on stored procedure override ... applictaion's user account permissions to the stored procedure which will ... Will try to deny access through the query for SQL Server 2005 and 2000. ... Remove everyone that you don't want rom an Administrator Groups ...
    (microsoft.public.sqlserver.security)
  • Re: Stored procedure loses permissions daily
    ... Columnist, SQL Server Professional ... Senior Database Administrator ... > I have a stored procedure in a SQL 2000 database that loses all of the ... permissions back to what it should be -- very frustrating. ...
    (microsoft.public.sqlserver.security)
  • Re: SQL 2000 Sproc Role security bypassed when using QBF?
    ... procedures relies on the fact that the stored procedure and the tables ... to grant permissions through stored procedures than ownership chaining. ... This article also discusses ownership chaining in more detail. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.security)
  • Re: Chain of ownership
    ... What will you do when John leaves the company?... ... the database which has permissions on the Stored procedure AND permissions ... Wayne Snyder, MCDBA, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Minimum permissions required to CREATE PROCEDURE
    ... You can grant statement permissions via GRANT. ... fixed database roles can also create procedures owned by other users. ... Permission to execute a stored procedure is given to the ...
    (microsoft.public.sqlserver.security)