Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I don't think there is a reliable way to setup security based on the client connecting. After all, if he has a login for SQL Server he could use any client to connect.
–
a_horse_with_no_nameSep 27 '12 at 6:59

So you realise that he could already connect using SSMS (or any other client), if his Windows account has permission to modify data?

Logon triggers won't work for this - you can't switch context.

Regular DML triggers are nasty for this, because they have added side-effects.

Hopefully your application uses stored procedures to make its changes, and you can make sure the user has EXECUTE permission on them, and then you can DENY the other permissions accordingly, relying on Ownership Chaining (but not Cross-Database Ownership Chaining) to allow the application to do its stuff.

You can put traces in place to audit what he does when connected. Hopefully your applications set the ApplicationName property of their connections, so that you can ignore their activity. Of course, if he knows what he's doing, he could create connections that mimic that same ApplicationName, circumventing your checks.

You could ensure they only use their 'special' account for ssms using a logon trigger. Something like this:

CREATE TRIGGER ssms_specific_users_only
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
declare @appname sysname
declare @ucheck integer
select @appname=program_name from sys.dm_exec_sessions where session_id=@@SPID
select @ucheck=COUNT(1) from ssms_user_table where login=SUSER_SNAME()
if (@appname='Microsoft SQL Server Management Studio - Query' AND @ucheck=0)
BEGIN
ROLLBACK
END
END

That's not tested unfortunately as I don't have a box to hand to test it on, but it's roughly correct I believe.

In my experience once one user's got this ability there'll be more, so I've envisioned using a table to hold all the users who are allowed to do this. So it checks for the connecting program's name, and then sees if the user is in a list of allowed users, if they aren't then it rolls back the connection attempt.

BTW, if the user already has permissions to modify data and connect with their other account, is the only thing stopping them from using SSMS the fact they don't have the app on their desktop? That could be a bit of a security hole if they ever discover the link to download it........