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: Bulkload account permissions
    ... I've been creating and giving permissions at ... Script to create account: ... CREATE USER myuser FOR LOGIN myuser WITH DEFAULT_SCHEMA = TARGET; ... GRANT SELECT TO ...
    (microsoft.public.sqlserver.xml)
  • 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: Bulkload account permissions
    ... I've been creating and giving permissions at ... Script to create account: ... CREATE USER myuser FOR LOGIN myuser WITH DEFAULT_SCHEMA = TARGET; ... GRANT SELECT TO ...
    (microsoft.public.sqlserver.xml)
  • 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)