SQL Server User Security - Best Practice

nigelr99 used Ask the Experts™

I'm setting up my first couple of applications using SQL server as a back-end and I would like some pointers on best practice in setting up user security etc.

Also, one application is based in Access whilst the other is ASP - should the users be setup in the same way or are there different considerations?

I'm testing just using the sa user so I've not investigated the options available as yet - any help appreciated.

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Data Architect
Obviously, lowest permissions are ideal - running the application as SA is fine for early development, but NEVER deploy an application using that login. I suspect you already know that, which is why you're asking :)

You'll have to use SQL Authentication, so you can set up a single user for the two different applications to share - there's nothing wrong with that, as long as they'll do the same tasks and sharing permissions isn't a problem. If only one of those applications will perform administrative tasks or delete from tables, then it's better to use different users - that way, you don't grant unnecessary rights to the other application.

Also, ensure that you're not doing query concatenation in your ASP application (or Access either, for that matter), though this can be mitigated by restricting the login's permissions as much as possible (so even if you are attacked, they can't do anything malicious because the user lacks the database rights).

That's somewhere to start - if you have specific questions, please feel free to ask and I'll answer them as best I can. There are tons of "best practices" and you can hardly read them all, so we'll try to help you out in the areas you're unfamiliar.
Please find the attachment might help out

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial