1

Using Microsoft SQL Server 2008, is it possible for a SQL Server account to be given privileges on a per-table basis within a database? For example, to have read access to all tables but write access to only one of the tables.

I am already familiar with the per-database security settings.

jscott
  • 24,204
  • 8
  • 77
  • 99
TJM
  • 13
  • 2

1 Answers1

2

Yes, you can use the GRANT statement, i.e.

GRANT UPDATE on myTable to myUserorRole

..or if the writing will be done via a stored procedure, grant execute to the proc.

Granting to roles rather than a specific account would be a good idea, then add the role to the account.

Check Books OnLine (BOL) for details.

SqlACID
  • 2,166
  • 18
  • 18
  • That looks promising but I get an error. If I have a table "dbo.MyTable" with user "MyUser" and I try "GRANT UPDATE, DELETE, INSERT ON dbo.MyTable TO MyUser;" it says "Cannot find the object 'MyTable', because it does not exist or you do not have permission." I have full permissions to the database but I am not an administrator for the db server, could that be a problem? – TJM Dec 23 '10 at 17:44
  • Found it, I can right-click on the table, go to properties, and play with permissions in there and it works. Thanks for the help, you got me going in the right direction. – TJM Dec 23 '10 at 17:54