Re: GRANT Select to all tables on a DB
From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 10/14/04
- Next message: David Gugick: "Re: Load file"
- Previous message: Dan Guzman: "Re: SQL2000: Could not locate file name ... in sysfiles"
- In reply to: Sam: "RE: GRANT Select to all tables on a DB"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 13 Oct 2004 23:20:43 -0500
If you need flexibility, generate and/or execute the script yourself rather
than relying on undocumented procedures. For example:
SET NOCOUNT ON
DECLARE @GrantStatement nvarchar(500)
DECLARE @LastError int
DECLARE GrantStatements CURSOR LOCAL FAST_FORWARD FOR
SELECT
CASE
WHEN OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsView') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 THEN
N'GRANT SELECT ON ' +
QUOTENAME(USER_NAME([ob].[uid])) + '.' + QUOTENAME([ob].[name]) +
' TO MyRole'
WHEN OBJECTPROPERTY([ob].[id], 'IsScalarFunction') = 1 THEN
N'GRANT EXECUTE ON ' +
QUOTENAME(USER_NAME([ob].[uid])) + '.' + QUOTENAME([ob].[name]) +
' TO MyRole'
ELSE
N''
END
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 "Sam" <Sam@discussions.microsoft.com> wrote in message news:A411570B-8F1B-46F7-8C88-9A00702C125A@microsoft.com... > Are there similar commands to iterate through all the Stored Procs on a > database, as well as all the views? Thank you again. > > Sam > > "SangHunJung" wrote: > >> Sam, >> For all tables in one DB, for example,pub db >> use pub >> go >> sp_msforeachtable 'grant select on ? to RO' >> >> For all DBs, sp_msforeachdb will do. >> >> Cheers, >> SangHunJung >> "Sam" wrote: >> >> > I have three main database files on a SQL 2000 server. Each database >> > has >> > about 200 tables. I need the ability to easily give a user SELECT for >> > all >> > tables in each database. I can use the GUI, but it takes way too long. >> > Please >> > help me figure out an easy way to enumerate all tables in the database, >> > so I >> > can construct a GRANT Select statement. >> > Thanks. >> > S
- Next message: David Gugick: "Re: Load file"
- Previous message: Dan Guzman: "Re: SQL2000: Could not locate file name ... in sysfiles"
- In reply to: Sam: "RE: GRANT Select to all tables on a DB"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
