Grant on all tables

Grant on all tables

• Dados

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

  
  
  

• John Mac

  Very cool... thank u
  
  
  

• Darshita

  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.

  
  
  
• Grant on all tables