Grant permissions

[nirmalsp]

Hi

I need to write a sibgle script to Grant select,insert,update,delete rights to multiple tables
(about 100)

& i don't want to Execute the same script agin & agin
by changing the table name
" Grant select on Custoemers to Management"
"Grant select on products to Management"

can u guys help me with this

 

[tlbroadbent]

You can use the stored procedure sp_msforeachtable to perform operations on every user table.

Example: Grant permissions on all tables in the database Inventory to the user Management.

use Inventory
go
exec sp_msforeachtable
'Grant select, update, insert, delete on ? to management'

However, it is actually much easier in SQL 7 and higher to assign the user to the db_datareader and db_datawriter roles. This will grant select, update, insert and delete permissions on all tables and views to the user.

exec sp_addrolemember
@rolename = 'db_datareader',
@membername = 'management'
go
exec sp_addrolemember
@rolename = 'db_datawriter',
@membername = 'management'
go Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.