I'm trying to limit the permissions granted to the people using the databases through the websites (we have one webuser per web application). Currently that web user is part of the db_owner role, but I don't think that's right. That means any user who's accessing the database through our website has unrestricted access on that database (through any security holes in our website), right? Someone in our IT department told me that our ASP.NET websites will "break" if we remove the web users from the db_owner roles. Any ideas or suggestions?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Proper permission setup for use with an ASP.NET web app? 1
- Thread starter keyser456
- Start date
-
1
- #2
mrdenny
Programmer
- May 27, 2002
- 11,595
Who ever told you that ASP.NET users need to be in the db_owner role is full of it.
Best practice is to create a database role, add the account that the ASP.NET user will be using into that role. Grant that role the specific rights that it needs within the database. Remove the application from all other roles except public.
Make sure that no rights are granted to public except the default rights granted by SQL Server.
I also recommend DENYing the select right to the sysfiles and sysobjects tables. This will prevent people who break in from finding out what drive the data is stored on, and what the names of the objects are.
It's also best to have the app only access the database through stored procedures. That way no direct access to the database tables is required.
Denny
MCSA (2003) / MCDBA (SQL 2000)
--Anything is possible. All it takes is a little research. (Me)
![[noevil]](/data/assets/smilies/noevil.gif "[noevil] [noevil]")
Best practice is to create a database role, add the account that the ASP.NET user will be using into that role. Grant that role the specific rights that it needs within the database. Remove the application from all other roles except public.
Make sure that no rights are granted to public except the default rights granted by SQL Server.
I also recommend DENYing the select right to the sysfiles and sysobjects tables. This will prevent people who break in from finding out what drive the data is stored on, and what the names of the objects are.
It's also best to have the app only access the database through stored procedures. That way no direct access to the database tables is required.
Denny
MCSA (2003) / MCDBA (SQL 2000)
--Anything is possible. All it takes is a little research. (Me)
- Thread starter
- #3
Thanks!
As far as using only stored procedures, I would love nothing more than to use that method, but we have so many websites in house (all of them super important... /sarcasm), we have to resort to quick and dirty dynamic SQL methods.
As far as using only stored procedures, I would love nothing more than to use that method, but we have so many websites in house (all of them super important... /sarcasm), we have to resort to quick and dirty dynamic SQL methods.
mrdenny
Programmer
- May 27, 2002
- 11,595
no problem.
Denny
MCSA (2003) / MCDBA (SQL 2000)
--Anything is possible. All it takes is a little research. (Me)
Denny
MCSA (2003) / MCDBA (SQL 2000)
--Anything is possible. All it takes is a little research. (Me)
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question