Grant on all tables

Hi,

is there a way to grants object privileges on
all tables of database to an user?

like this:

grant select, insert, update, delete on <all tables> to usernamedb
go

thanks!!!!

No. You can add user to db_datareader and db_datawriter roles. This will however provide SELECT, INSERT, UPDATE and DELETE permission on tables/ views / table-valued functions etc. See Books Online for more details on the permissions / roles. Also, you should create a group/role and grant permissions to it instead of directly to the user. This is easier to manage and control.

Alternatively, you can write few lines of code that loops through the desired objects and grants necessary permissions on each object using dynamic SQL.

|||

In 2005 you can grant access to a schema's set of objects:

grant select, insert, update, delete on schema::dbo to bob

For example, in the AdventureWorks DB:

use adventureWorks
go
create user bob without login
go
--first prove no accss
execute as user='bob'
go
select * from production.product --will error
go
revert
go
grant select, insert,update, delete on schema::production to bob
go
execute as user='bob'
go
select * from production.product --will work
go
revert
go

Note that this gives access to table valued user-defined functions also...

|||Very cool... thank u