Re: GRANT permission to lots of tables and sp to db user
From: Siri (Siri_at_discussions.microsoft.com)
Date: 10/27/04
- Next message: Nikolay Petrov: "Stored procedures security"
- Previous message: Dan Guzman: "Re: GRANT permission to lots of tables and sp to db user"
- In reply to: Dan Guzman: "Re: GRANT permission to lots of tables and sp to db user"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Wed, 27 Oct 2004 05:35:13 -0700
Thank you very much! This really helped!
Siri
"Dan Guzman" wrote:
> 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
> > ?
> >
>
>
>
- Next message: Nikolay Petrov: "Stored procedures security"
- Previous message: Dan Guzman: "Re: GRANT permission to lots of tables and sp to db user"
- In reply to: Dan Guzman: "Re: GRANT permission to lots of tables and sp to db user"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
|
