permission for application account

Question

Hi, I have a question for database permission.

What is the best practice to setup permissions for an application account?

I know it is to stick on the rule : that we should only grant permissions needed. keep minimum as possible.

And db_owner is not a good option even the database is only used internally.

I usually setup a role that can execute stored procedures and functions.

But in recent .net applications, developers use entity frame work which generate SQL commands in the code. So they may not necessarily use stored procedures.

In this case they also need have select, update, insert, delete permissions to the database for the application execute account.

What is the best practice to setup permissions in this case?

Thanks,

---

SQLFriend

Answer 1

Best practice is to have a security policy within the organisation. If that policy is all stored procedures, it is all stored procedures, and developers will have to comply.

If you don't have a security policy, you will have to dance along the developer's pipes. Which means that in this case, the application account needs to be member of db_datareader and db_datawriter.  You will still grant EXECUTE permission on any stored procedure.

I can't say that I'm a great fan of EF or any other OR/M, but admittedly one advantage is that if used consistently, the risk for SQL injection is about nil.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

>>>>What is the best practice to setup permissions in this case?

In that specific case (especially if it uses dynamic sql as well) I would go with db_owner.... BUT better option would be re-write all ad-hoq queries as stored procedures  if it is possible in your case.

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 3

Hello ,

Read the white paper :SQL Server 2012 Security Best Practices - Operational and Administrative Tasks.

---

Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

Answer 4

Thanks all.

If we still use entity framework, without using a lot of procedures, it seems I should either grant the application account db_reader, db-writer, execute to all OR db_ower.

I will read the security best practice, hopefully it will tell me something about question regarding Entity framework database.

---

SQLFriend