Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.