Re: How can permissions of multiple tables be changed at once?
From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 09/21/04
- Next message: Prasanth: "How to Update Identity Columns"
- Previous message: CW3: "SQL2K and SQL2005 Beta on one server?"
- In reply to: bing: "How can permissions of multiple tables be changed at once?"
- Next in thread: bing: "Re: How can permissions of multiple tables be changed at once?"
- Reply: bing: "Re: How can permissions of multiple tables be changed at once?"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 20 Sep 2004 22:41:15 -0500
Below is a sample script you can customize and run to apply mass
permissions.
SET NOCOUNT ON
DECLARE @GrantStatement nvarchar(4000)
DECLARE GrantStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
N'GRANT SELECT ON ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) +
N' TO MyRole'
FROM INFORMATION_SCHEMA.TABLES
WHERE
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0 AND
TABLE_TYPE = 'BASE TABLE'
OPEN GrantStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM GrantStatements
INTO @GrantStatement
IF @@FETCH_STATUS = -1 BREAK
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 "bing" <bing@discussions.microsoft.com> wrote in message news:908EE4BF-CF98-46CD-A543-36634FA87DE7@microsoft.com... >A user database has a lot tables. How can I change all the tables to have > the same permissions (e.g. SELECT, INSERT, etc.) at once rather than > manually > do it by going through table by table? > > Thanks in advance for any help, > > Bing
- Next message: Prasanth: "How to Update Identity Columns"
- Previous message: CW3: "SQL2K and SQL2005 Beta on one server?"
- In reply to: bing: "How can permissions of multiple tables be changed at once?"
- Next in thread: bing: "Re: How can permissions of multiple tables be changed at once?"
- Reply: bing: "Re: How can permissions of multiple tables be changed at once?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
