We have a custom application that updates a database. I wish to ensure that all access to the database must be done via the custom application. With that in mind I propose using a loginless user to prevent user from directly accessing the database using their Windows id. However, how do I prevent them from loging onto sql via ssms and then doing an 'execute as user 'loginless user name'?
As long as someone doesn't have permissions to the server, they can't connect to it. If they have permissions to the server, but not to the database, they can't connect to the database. If they have permissions to connect to both, then you just have to restrict what they can do on the database by using DENY on privileges. If, however, you're in the situation where you've set everyone up as 'sa' there's nothing you can do.
I think I understand the situation (or at least I hope that I do). @Grant Fritchey is absolutely correct here, but I would like to add my 2 cents. If your custom application uses SSPI so every application user connects to the database as respective windows principal then it is just plain evil and should be avoided if at all possible. I understand that it is somewhat convenient because - there is no need to maintain passwords - SQL Server takes care of denying connection to someone who is no longer an active AD user - Placing members of a certain AD group into the role is handy for granular permissions The problem with SSPI is that the connection pooling is suffering greatly. It is much easier for SQL Server to manage the pool if every application user has the **same DNA**. If this is the case in your scenario then you can solve the problem you have by simply denying impersonate permission to the users who login to SSMS as windows principals while still preserving the ability of the SQL Server login used by the application to impersonate your loginness account which has appropriate permissions to do whatever your procedures used by the application are doing. For example, suppose that all developers connecting to the SSMS using windows authentication are members of a certain role, and while you need the account used to connect the application to use **execute as**, you don't want the developers to run any queries as your loginness user. deny impersonate on user::your_loginness_user to some_devs_role cascade; go You don't necessarily need a **cascade** option, it all depends on whether explicit grant permissions were used for any specific users. In case if your application uses SSPI, there is no way to get what you need as @Grant Fritchey has already pointed out in his answer. Oleg
You can move the EXECUTE AS code to a stored procedure that your application calls on the users' behalf and don't give out the actual LOGINLESS USER name. Anyone allowed limited access could also be put in an AD group that has DENY ALL ON VIEW DEFINITION.