Re: GRANT permission to lots of tables and sp to db user

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 10/27/04 

Date: Wed, 27 Oct 2004 07:22:20 -0500

You can use a script like to example below to grant mass permissions
according to your requirements.

SET NOCOUNT ON

DECLARE @GrantStatement nvarchar(500)
DECLARE @LastError int

DECLARE GrantStatements CURSOR LOCAL FAST_FORWARD FOR
SELECT
    N'GRANT ' +
    CASE
        WHEN OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
            OBJECTPROPERTY([ob].[id], 'IsView') = 1 THEN
            N'SELECT, INSERT, UPDATE, DELETE'
        WHEN OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1 OR
            OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 THEN
            N'SELECT'
        WHEN OBJECTPROPERTY([ob].[id], 'IsScalarFunction') = 1 OR
            OBJECTPROPERTY([ob].[id], 'IsProcedure') = 1 THEN
            N'EXECUTE'
    END +
    N' ON ' +
    QUOTENAME(USER_NAME([ob].[uid])) + '.' + QUOTENAME([ob].[name]) +
    N' TO MyRole'
FROM
    sysobjects ob
WHERE
    OBJECTPROPERTY([ob].[id], 'IsMSShipped') = 0 AND
    (OBJECTPROPERTY([ob].[id], 'IsProcedure') = 1 OR
    OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
    OBJECTPROPERTY([ob].[id], 'IsView') = 1 OR
    OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 OR
    OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1)
OPEN GrantStatements
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM GrantStatements INTO @GrantStatement
    IF @@FETCH_STATUS = -1 BREAK
    RAISERROR (@GrantStatement, 0, 1) WITH NOWAIT
    EXECUTE sp_ExecuteSQL @GrantStatement
END
CLOSE GrantStatements
DEALLOCATE GrantStatements 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Siri" <Siri@discussions.microsoft.com> wrote in message 
news:51FFF23E-3543-4A4C-B6FE-8FCE4026CCA3@microsoft.com...
> Is it possible to grant permissions (select, insert, delete, update, exec)
> to a db user to all tables and all stored procedures in a specific db in 
> an
> easy (lazy!) way?
>
> I mean, except for clicking in all permission checkboxes in Enterprise
> Manager or writing a huge sql script like
>
> grant select, insert, delete, update
> on mytable1
> to myuser
> grant select, insert, delete, update
> on mytable2
> to myuser
> ....
> grant exec
> on mySP1
> to myuser
> grant exec
> on mySP2
> to myuser
> ....
>
> ?
> Is there another way, like
>
> GRANT select, insert, delete, update
> on AllMyTables
> to myuser
> GRANT exec
> on AllmySP
> to myuser
> ?
>

Relevant Pages

  • Re: GRANT all on all to User ????
    ... The GRANT statement syntax doesn't accept wildcard characters. ... DECLARE @UserName sysname ... DECLARE GrantStatements CURSOR LOCAL FAST_FORWARD ...
    (microsoft.public.sqlserver.security)
  • Re: USERS group has the ability to change security permissions???
    ... Please use the Advance view in the NTFS permissions dialog to ... When there is a generic grant and a special grant to the same entity ... the RESULT: user level access can change NTFS ...
    (microsoft.public.win2000.security)
  • Re: DCOM Event ID 10015
    ... Grant the user permissions to start the COM component ... Run the MPSRPT_DirSvc.exe on the server box. ... Microsoft CSS Online Newsgroup Support ...
    (microsoft.public.windows.server.sbs)
  • Re: NTFS woes
    ... "In some cases with a grant of Full is reduced ... NTFS permissions dialog. ... check Allow Delete Subfolders and Files ... Explicit Deny Delete on file does not work (user can still delete ...
    (microsoft.public.windows.server.security)
  • Re: NTFS woes
    ... "In some cases with a grant of Full is reduced ... NTFS permissions dialog. ... check Allow Delete Subfolders and Files ... Explicit Deny Delete on file does not work (user can still delete ...
    (microsoft.public.windows.server.security)