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: 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: 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: 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)