July 22, 2008 at 8:12 am
I have an application that runs a script against our company database. I was curious what was the minimum permissions I could give the sql user. The only thing the script does runs a query against 4 tables.
Thanks
July 22, 2008 at 8:17 am
Login to the server. Access to the database with Select only on those 4 tables.
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
July 22, 2008 at 11:40 am
the datareader role should grant select on tables.
of course you could modify the public role but i wouldnt recommend that
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 22, 2008 at 11:57 am
Perry Whittle (7/22/2008)
the datareader role should grant select on tables.of course you could modify the public role but i wouldnt recommend that
This would work but provides more
than the minimum rights, assuming there are
more than 4 tables in the db.
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
July 22, 2008 at 12:09 pm
datareader only applies to user tables so should be safe
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 22, 2008 at 12:18 pm
Perry Whittle (7/22/2008)
datareader only applies to user tables so should be safe
Sure, but it applies to ALL user tables in the database. Using the datareader role is usually a mistake as it does not apply the least permissions needed. If there are 6 tables like this:
Employees
EmployeeAddresses
EmployeeSalaries
EmployeeSkills
Skills
EmployeeDiscipline
Assigning a user to the datareader role grants rights to all the tables and only HR should have rights to salaries and discipline, so if the application only requires rights to Employees and EmployeeAddresses in order create a mailing list then that's all the role\user should have select rights on.
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
July 22, 2008 at 12:26 pm
Jack Corbett (7/22/2008)
Perry Whittle (7/22/2008)
datareader only applies to user tables so should be safeSure, but it applies to ALL user tables in the database. Using the datareader role is usually a mistake as it does not apply the least permissions needed. If there are 6 tables like this:
Employees
EmployeeAddresses
EmployeeSalaries
EmployeeSkills
Skills
EmployeeDiscipline
Assigning a user to the datareader role grants rights to all the tables and only HR should have rights to salaries and discipline, so if the application only requires rights to Employees and EmployeeAddresses in order create a mailing list then that's all the role\user should have select rights on.
in that case i would explicitly deny select on those few tables to the appropriate users\groups and the users would still have read to all other tables
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply