How can permissions of multiple tables be changed at once?

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
You really can't. You could write some scripts to do the work, but by the
time you finished doing that, you would probably have spent more time than
doing it by hand.
You should create roles however and add your users to those roles and then
apply permissions to the roles.
There are already a few roles pre-created for you that can help you with
this. For example, db_datareader and db_datawriter.
That should give you your basic INSERT, SELECT etc. permissions on the bulk
of your database objects.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"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
|||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
|||Thanks all who replied. We do use roles to manage users and permissions.
I'll customize and try the script Dan kindly provided.
Bing
"Dan Guzman" wrote:

> 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...
>
>