I have a connection string in my asp.net code that looks at a SQL Server 2005 database. I created a UserName called WebUser and granted him db_datawriter and db_datareader in SQL Server 2005. Both under Login and under the database the user must be able to read and be able to make updates? Is this all I need or the correct thing to do?

Connection string will look like this.

<add name="ConnectionStringName" connectionString="DataSource=localhost;
 Initial Catalog=DBName;User ID=WebUser; Password=mypassword; 
 Integrated Security=FALSE" 
  • 1
    +1 for asking if this is the correct thing to do. – mrdenny Aug 5 '09 at 10:36
  • Is there a specific reason why "Integrated Security=FALSE"? This adds vulnerabilities. – Rob Garrison Aug 5 '09 at 19:45
  • @Rob......No, must i leave this out?? – Etienne Aug 6 '09 at 6:57

It you want to risk your database being downloaded or deleted by a hacker than yes that's all you need to do.

If you'd like your database to be secure from all sorts of attacks, then you'll want to create stored procedures to handle all the inserting, updating and deleting of data in the database, then call those stored procedures instead of accessing the tables directly.

Then remove the user from the db_datareader and db_datawriter fixed database roles. Create a new role (you can call it anything you like) and make your user a member of that role. Then grant that role execute access to all the stored procedures in the database. The code to grant a role (or user for that matter) access to run a stored procedure is:

GRANT EXEC ON {ProcedureName} TO {User Or Role Name}

mrdenny's comments are quite true if you can make it happen. If you're developing a system from scratch, use SPs and don't grant access to the base tables/views.

In many cases, this is not a workable solution. If your app needs access to the base tables/views, make sure it is the minimal rights. Use individual rights on the schema rather than db_datareader and db_datawriter.

Your application should be running under an app-only Windows login and will likely need to be granted SELECT, INSERT, UPDATE, DELETE, EXECUTE, and REFERENCES to the schema.

If you only need access to the tables/views for reporting, run the reports under a different user and grant that user the CRUD rights.

Your Answer

