November 22, 2007 at 7:00 pm
I have a ASP.Net web application that uses SQL server 2005 as the database. Each user of my web application will be using the application over the internet, therefore they will NOT have a windows login ID that I can use to connect them to SQL server 2005. Therefore I have created a SQL server login and I am using it in my web application connection string.
My web application exclusively uses stored procedures for ALL data access to the database, it does not execute ANY direct SQL statements against any tables in the database.
I want to setup the SQL login I am using in my web application to have rights only to execute stored procedures. I do NOT want to allow this login any type of access to the tables. I would prefer to do all this with just one SQL login.
If someone one could give me a step by step guide on how to accomplish this, I would greatly appreciate it!
Thanks to all that reply!
November 23, 2007 at 11:56 pm
Hi,
I am not sure you can allow user to execute Stored Procedure (which probably does a lot of Select/Update/Insert) whithout giving the user any type of access to tables. For me the user will be at least member of db_dlladmin.
Regards,
Ahmed
November 24, 2007 at 12:04 am
Sorry DaGmen
I think I was wrong check the following link http://www.mssqltips.com/tip.asp?tip=1203
As such, stored procedure based access to SQL Server from your front end applications offers the following benefits:
- Security based on the object that can be assigned rights with all business rules incorporated
- No direct access to tables or views
Regards,
Ahmed
November 24, 2007 at 2:49 am
just add the login to your db (public) and then
you can grant it exec on schema.
This way _all_ schema bound functions and procedures are available for the login.
Also keep in mind to use the EXECUTE_AS_Clause with create or alter procedure or function. This way you can have the proc run with the authority of the proc owner.
Check BOL.
You might as well have your website using a windows account for anonimous logins (iis website properties) in stead of a sql-userid.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 24, 2007 at 3:30 am
Hi ALZDB,
If I want to create the user JustProc on AdVentureWorks datatbase.
Please Just confirm the following:
1. Connect to an instance as the sysadmin
2. Create a new login JustProc (SQL Server login)
3. Add this login to the AdventureWorks database without any permissions
4. Open a new query window and connect it as sysadmin,Switch database context to AdventureWorks
5. Execute the followimg command GRANT EXECUTE ON SCHEMA::dbo TO JustProc
Regards,
Ahmed
November 24, 2007 at 10:34 am
Thanks for the replies, I am going to try out the suggestion in that article (from the link). If anyone else has any other ideas please do let me know.
November 24, 2007 at 6:05 pm
Video file, secure site using Membership and Roles.
...
http://www.asp.net/learn/videos/
See also,
http://asp.net/community/projects/
Old Starter Kits 1.1.
November 25, 2007 at 10:31 am
Ahmed Bouzamondo (11/24/2007)
Hi ALZDB,If I want to create the user JustProc on AdVentureWorks datatbase.
Please Just confirm the following:
1. Connect to an instance as the sysadmin
2. Create a new login JustProc (SQL Server login)
3. Add this login to the AdventureWorks database without any permissions
4. Open a new query window and connect it as sysadmin,Switch database context to AdventureWorks
5. Execute the followimg command GRANT EXECUTE ON SCHEMA::dbo TO JustProc
Regards,
Ahmed
The sequence is correct.
However, you need to pay attention regarding the context which will be active when the proc is being executed.
Check out BOL for more info regarding EXECUTE_AS_Clause
http://msdn2.microsoft.com/en-us/library/ms188354.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply