SQL Server Security Help

[Armusis]

First off I am very new to SQL Server and its security features.
I have a sql server database that needs to be secured. I need to add users and user groups to it. I am going to create a web application to access the database. Content on the webapp will be dynamically generated based on the currently logged on user’s group. What is the best way to handle security for this scenario? Should I use SQL server’s built in security and if so, how do I set it up (remember I’m new) or should I create my own security tables? Right now I just use the sa/password login to access the database. I would appreciate any help.

 

[ecobb]

Rule #1. NEVER use the SA account to access the SQL Server from a web site. It's OK it you're building the database and working in Enterprise Manager (but be careful!).

If you're going to be accessing the SQL Server via a web site, I'll assume you'll be using something like ColdFusion or ASP. What I've always done with my web apps. is set up one or two users on the SQL Server and give them only the permissions they need, nothing extra. Then, I have my web app handle the security and permissions for all of the web users, and from there determine which account they access the SQL Server with.

For Example, let's say I have 2 accounts for my database, one with read only permissions and one with full permissions. When my users log into my web site, the site will know to run queries for the general users using the read only account, and run queries for administrators using the full account.

Another advantage to this is you can have hundreds of users for your web site, but only access the SQL Server with one or two accounts (or however many you feel you need).

Hope this helps!

 

[Armusis]

Thanks. This is great information.