August 8, 2008 at 12:13 pm
Im an Oracle DBA attempting to muddle my way into SQLServer.
We have a database with multiple tables... of course.
We created a user and that user needs access to only 1 table in the dbo schema. We will call that table 'table1'
The user could see the table and its contents but could not insert, update or delete even though when looking on the permissions page of 'table1' properties, it was granted the privileges.
Then looking at the 'Database Role Membership' I saw that the user had DenyDataWriter and DataReader. So I revoked DenyDataWriter and now the user can insert, update and delete on 'Table1'.
So I might have answered my own question, as far as why the user could not insert, update and delete. But my concern now is, Will the user be able to update, insert or delete data in any other tables in the database besides 'Table1'? If so how do I ensure they cannot?
August 8, 2008 at 12:16 pm
In SQL Server the best way to handle security is through roles. You create a role and Grant appropriate permissions. Then you add users to that role.
In your case if that user has only been granted rights on that single table and are not in any roles that grant them other rights then they cannot do anything on any other table.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply