What SQL user to use for the connection string?

Question

I’m using .Net 3.5 and SQL Server 2008 Express.

Should I use the administrator user for the connection string,

Or should I create a new user with limited permissions?

If I need to create a new user for the connection string:

What security permissions should I grant him?

How do I set those permissions?

Thanks.

Answer 1

You must definitely create a specific SQL login, which you will use for database hits in your application.

Access only to the database it uses.

Assign specific write/read permissions according to the logic you have.

Answer 2

Never use Admin users account. Decide what all permission is required by your application then create the user based on that. I dont work mostly with SQl servers but I don't think there is a definite rule for this. It depends on application and the situation.

if the application is used to display only records then grand only read access.

Please check this post too

Enterprise Connection String Management in ASP.NET - Best Practice?

Answer 3

never use the sysadmin (sa) account - EVER!! create a new account, probably start with a member of the Public group, which is the default and then work from there.

Also, do not embed the pwd in the connection string - rather make it part of a config file; not only is it more secure, it's easier to manage.

In fact, you should really store the entire connection string in a config file.