Limited user

I'd like to create a limited user on SQL Server 2005 to minimize any possible problems with my ASP.NET 2.0 website. My question is how limited can I make that user? What exactly does it need to be able to do?

(In case it makes a difference, my site utilizes membership, profiles, and roles, as well as some custom tables and stored procedures that I created.)

Thanks in advance for any advice! :-)

I usually create a SQL Server login that only has Execute permission on the stored procedures that are needed to run the site. That way, they have no other permissions as far as SQL Server is concerned, which means that you are largely protected against SQL Injection through the stored procedures, and also by the fact that the user has no permissions to do anything but fire the stored procedures you allow them to execute.

There is no SQL in the app itself. It's restricted purely to stored procs.

|||

Thanks for the reply! Are there specific stored procedures that a typical user must have execute permission for or do they need to have permission to execute all of them? I'm really concerned about security so I'd like to keep things as limited as possible without removing functionality or causing errors.

|||

Since you are only likely to create stored procs in response to the needs of the site, the user will need execute permission on all of them. If you create a stored proc that returns data to the page but don't give the user permssions on it, your site will fail.

DO NOT grant the user blanket EXECUTE. Grant execute on each proc individually eg

CREATE PROCEDURE MyProc

AS
BEGIN
SELECT field 1 FROM table1
END
GO
Grant Execute On MyProc to MyUser
GO

You can almost think of it as the Page is the user. The page needs to be able to call certain procs in order to display/insert/edit data. So you give it permission to run the procs.