Re: Grant permissions

From: Arun (arunkkk_at_yahoo.com)
Date: 09/28/03

  • Next message: Arun: "Re: Grant permissions" 
    Date: Sun, 28 Sep 2003 02:31:31 -0700

    That was cool thanks

    >-----Original Message-----
    >Although you can't do this one statement, you can
    dynamically generate
    >and execute GRANT statements. SQL 2000 script example:
    >
    >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 MyRole'
    >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
    >
    >-----------------------
    >SQL FAQ links (courtesy Neil Pike):
    >
    >http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
    >http://www.sqlserverfaq.com
    >http://www.mssqlserver.com/faq
    >-----------------------
    >
    >"Arun" <arunkkk@yahoo.com> wrote in message
    >news:12ac01c3826c$977175a0$a301280a@phx.gbl...
    >> Can I grant execute permission to all stored procedures
    in
    >> a database to an user in a single statement
    >>
    >> grant execute on all to <user name> doesn't work !
    >
    >
    >.
    >

  • Next message: Arun: "Re: Grant permissions"