Restrict access

SQL Server Administration
1

Hello,
I need to restrict people from using Excel to connect to other databases.

Situation: User have an Excel program that gets data from a specific database using Windows authentication. This is okay, however what if the user then tries to get data from a different database? Notice that the user does have permission to other databases that they should only access via .NET front ends.

I have looked into a trigger to catch information (note that MS BOL has error in function EVENTDATA example 3).
This gives me information about the application however I am not sure how best to implement it.

Thank you for your time.

2

Note that Excel is just another .NET application (written in C++.NET IIRC) so SQL cannot differentiate. However, you could use a logon trigger and look at the application name.

see Application Name for SQL Server Connections for details

3

Thank you gbritton.
You can actually get a little information from sys.dm_exec_sessions which tells me that Office is connecting and who.

4

Yes, Excel sets the application name in the connection string. You can use that in a trigger to control access