Re: Execute premmition

From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 12/24/03 

Date: Wed, 24 Dec 2003 14:33:21 -0600

Here's a script like the one Russell alluded to. This will grant the
specified role execute permissions on all user stored procedures in the
current database:

DECLARE @MyRole sysname
SET @MyRole = 'MyRole'
DECLARE @GrantStatement nvarchar(4000)
DECLARE GrantStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
    'GRANT EXECUTE ON ' +
    QUOTENAME(USER_NAME(uid)) +
    '.' +
    QUOTENAME(name) +
    ' TO ' +
    @MyRole
FROM sysobjects
WHERE
    OBJECTPROPERTY(id, 'IsProcedure') = 1 AND
    OBJECTPROPERTY(id, 'IsMSShipped') = 0
OPEN GrantStatements
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM GrantStatements INTO @GrantStatement
    IF @@FETCH_STATUS = -1 BREAK
    EXEC(@GrantStatement)
END
CLOSE GrantStatements
DEALLOCATE GrantStatements
GO 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy Goldhammer" <roygoldh@hotmail.com> wrote in message
news:%23G2YPZlyDHA.1736@TK2MSFTNGP09.phx.gbl...
> Thankes russell
>
> But i don't like to work hard for this
>
> there is probebly system store procedure that add the execution premmition
> for some role.
>
> There is also option to know which procedure is has already grant execute
so
> the procedure i want to build will allow me to update the role
>
> Can you help me on it?
>
> any help would be useful
> "Russell Fields" <RussellFields@NoMailPlease.Com> wrote in message
> news:#wbvU0kyDHA.3224@tk2msftngp13.phx.gbl...
> > Roy,
> >
> > There is not a predefined role for stored procedures, so yes you will
need
> > to create one.
> >
> > Of course, you could write a little bit of code to generate a GRANT for
> each
> > stored procedure in the database to your general role.  (Just need to
> > remember when you add stored procedures to do it again.)
> >
> > Russell Fields
> > "Roy Goldhammer" <roygoldh@hotmail.com> wrote in message
> > news:uzZ9XykyDHA.3436@tk2msftngp13.phx.gbl...
> > > Hello there
> > >
> > > I've add new user to my sql server.
> > >
> > > AS the default he got the public role.
> > >
> > > I add db_datawriter and db_datareader to the user.
> > >
> > > Now the user can enter data and read data but he can't execute store
> > > procedures.
> > >
> > > Is there a role for executing stored procedures?
> > >
> > > if not is that mean that i have to create my owm role and pass stored
> proc
> > > one by one and allow it for the role?
> > >
> > > any help would be useful
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: Grant Execute right to the group of users
    ... Below is a script that will grant execute permissions to the specified role ... on all user procedures in the current database. ... > How could I grant the execute right to all stored procedures in a database for a group of users? ...
    (microsoft.public.sqlserver.programming)
  • Re: Execute premmition
    ... there is probebly system store procedure that add the execution premmition ... There is also option to know which procedure is has already grant execute so ... "Russell Fields" wrote in message ... > There is not a predefined role for stored procedures, ...
    (microsoft.public.sqlserver.security)
  • Extreme performance issues (SQL Server 2000/ADO.NET/C#)
    ... same exact stored procedures and views, run in the same exact order, through ... system that runs SQL Server (a 4-cpu Xeons system with 2gigs of physical ... When I execute these steps manually through query analyser,, ...
    (microsoft.public.sqlserver.server)
  • Sybase, JDBC, AutoCommit, DDL IN TRAN
    ... having executing Sysbase stored procedures via JDBC. ... stored procedure which contains DDL and with AutoCommit set to false I ... The explanation for this behaviour I have found is that the JDBC ... I need to be able to set AutoCommit to false and execute multiple ...
    (comp.lang.java.databases)
  • Re: Sybase, JDBC, AutoCommit, DDL IN TRAN
    ... > having executing Sysbase stored procedures via JDBC. ... > stored procedure which contains DDL and with AutoCommit set to false I ... > The explanation for this behaviour I have found is that the JDBC ... > I need to be able to set AutoCommit to false and execute multiple ...
    (comp.lang.java.databases)