How to Restrict Acct Fr SQL Query Analyzer | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

How to Restrict Acct Fr SQL Query Analyzer

Is it possible to restrict connection to SQL Server based on the application used to connect? For instance, we don’t want to allow a specific SQL login name to use SQL Query analyzer to connect to a production database. However, the same SQL Login name is used by the application so we do not want to restrict access to the database objects. I understand this can be done using triggers but there is considerable performance degradation.
Hi there, The only way that I can think of is to have the application connect to the database using a very low privileged account (one that has only read access in master), and then it calls sp_setapprole to change its credentials to another account. This second account can’t be used by interactive query tools without knowing the password which would only be know to the application, developers and/or sys admins NOTE that application roles only have access in one database, and can’t access objects in another database without using guest accounts in the other databases (which would kind of defeat the purpose…)
Cheers
Twan
True, for more details check thishttp://www.sqlteam.com/item.asp?ItemID=864 page.
And Pros & Cons using App.roleshttp://www.sqlservercentral.com/col…rversecurityprosandconsofapplicationroles.asp
_________
Satya SKJ
Moderator
SQL-Server-Performance.Com
]]>