Re: SQL Accounts

From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 05/29/04

  • Next message: Paul Ibison: "Re: SQL Auth password length setting" 
    Date: Sat, 29 May 2004 10:06:50 -0500

    Below is a script that will grant execute permissions to the specified role
    on all user procedures in the current database.

    SET NOCOUNT ON

    DECLARE @GrantStatement nvarchar(4000)

    DECLARE GrantStatements CURSOR
    LOCAL FAST_FORWARD READ_ONLY FOR
    SELECT
        N'GRANT EXECUTE ON ' +
        QUOTENAME(ROUTINE_SCHEMA) +
        N'.' +
        QUOTENAME(ROUTINE_NAME) +
        N' TO Users'
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE
        OBJECTPROPERTY(
            OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
                N'.' +
                QUOTENAME(ROUTINE_NAME)),
            'IsMSShipped') = 0 AND
        OBJECTPROPERTY(
            OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
                N'.' +
                QUOTENAME(ROUTINE_NAME)),
            'IsProcedure') = 1
    OPEN GrantStatements
    WHILE 1 = 1
    BEGIN
        FETCH NEXT FROM GrantStatements
            INTO @GrantStatement
        IF @@FETCH_STATUS = -1 BREAK
        IF @@FETCH_STATUS = 0
        BEGIN
            RAISERROR (@GrantStatement, 0, 1) WITH NOWAIT
            EXECUTE sp_ExecuteSQL @GrantStatement
        END
    END
    CLOSE GrantStatements
    DEALLOCATE GrantStatements 

    -- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Gary" <clgary@yahoo.com> wrote in message
news:uz8GuICREHA.2876@TK2MSFTNGP09.phx.gbl...
> Hi Hari,
>
> Unfortunately, neither role grants execute.  I'd rather not have want to
go
> and add the execute priveledge to a given for a new procedure.
>
> Do you know if that is possible?
>
> That covers most of it,
> "Hari" <hari_prasad_k@hotmail.com> wrote in message
> news:e0Tv9f$QEHA.3580@TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > Assign the user db_datareader and db_datawriter  database fixed roles.
> >
> > Script is :-
> >
> > sp_addrolemember 'db_datareader','<db_user>'
> > go
> > sp_addrolemember 'db_datawriter','<db_user>'
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> > "Gary" <clgary@yahoo.com> wrote in message
> > news:eXkNSm1QEHA.3608@TK2MSFTNGP10.phx.gbl...
> > > Hi,
> > >
> > > I'm having a bit of trouble figuring out the how to setup roles that
> > > basically grant EXECUTE, INSERT, SELECT, UPDATE, DELETE  on a
database.
> > Im
> > > trying to prevent CREATE, ALTER, and DROP.  Does anyone have a
> reccomended
> > > practice of doing this?
> > >
> > >
> >
> >
>
>
  • Next message: Paul Ibison: "Re: SQL Auth password length setting"

    Relevant Pages

    • Re: Changing Permissions for all Stored Procs in a SQL2005 DB
      ... already created the database role, if I do "GRANT EXECUTE TO MyNewRole", ... In SQL Server 2005 it is very easy to set the execute rights to all ...
      (microsoft.public.sqlserver.security)
    • Re: Windows Services Security
      ... Are you debugging your SQL code on that database? ... you have to grant execute permission to the 'sp_sdidebug' stored ... Visual Studio help has some information about SQL debugging. ...
      (microsoft.public.dotnet.framework.component_services)
    • Re: Windows Services Security
      ... Are you debugging your SQL code on that database? ... you have to grant execute permission to the 'sp_sdidebug' stored ... Visual Studio help has some information about SQL debugging. ...
      (microsoft.public.dotnet.languages.vb)
    • Re: a bit tricky question
      ... Mike wrote: ... > it from database B under y schema. ... > How to work it out without grant execute to public? ...
      (comp.databases.oracle.server)
    • Re: GRANT Select to all tables on a DB
      ... generate and/or execute the script yourself rather ... OPEN GrantStatements ... "Sam" wrote in message ... >>> I have three main database files on a SQL 2000 server. ...
      (microsoft.public.sqlserver.server)