September 21, 2008 at 11:20 pm
In SQL 2005, I have a group of users for whom I need to set select-only permission on a table. These users need to have full rights on all tables except the one select-only table. Can anyone tell me the steps to configure the appropriate security?
September 21, 2008 at 11:43 pm
Hello
You can use deny permission(read or write or ..) on a particular object (Table in your case )
which can solve your requirement .
September 22, 2008 at 7:59 am
You just need to Grant Select on table to user/role instead of doing Grant All on Table to user/role.
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
September 22, 2008 at 10:59 am
I've granted Select permission to the table for a "view only" role (and added the specific db user as a member of the role). The Select permission appears to be applied to the role when I click OK, but then if I go back into the properties for the role, I can't see the table or its permissions on the Securables page.
After configuring Select permissions for my one table and logging in as the view-only user, I don't have Select access to ANY of the db tables--not even the table on which I applied the Select-only rights.
I have very little experience working with SQL Server security setup, so I'm sure I'm missing something.
September 24, 2008 at 12:39 pm
This is from a personal email. I choose to post any personal emails and answers to the forum which they apply so that everyone can benefit. Oh and also so my personal email doesn't get out there because I reply directly.
You had posted a reply to my SQL Server Central forum posting, but I'm still having problems configuring permissions.
I've granted Select permission to the table for a "view only" role (and added the specific db user as a member of the role). The Select permission appears to be applied to the role when I click OK, but then if I go back into the properties for the role, I can't see the table or its permissions on the Securables page.
After configuring Select permissions for my one view-only table and logging in as the view-only user, I don't have Select access to ANY of the db tables--not even the table on which I applied the Select-only rights.
I have very little experience working with SQL Server security setup, so I'm sure I'm missing something. Any help you can provide would be greatly appreciated.
Thanks in advance!
Instead of using the GUI to assign persmissions use T-SQL.
Create Role ViewOnly AUTHORIZATION dbo
Grant Select On TableName To ViewOnly
EXEC sp_addrolemember N'ViewOnly', N'UserName'
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
September 30, 2008 at 6:16 am
the datareader role should grant select to all tables
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply