2

A couple of weeks ago my boss connected to our production database via excel and was able to query the database. Because of how we're doing security to the database (AD groups) he should have access to the database. I'd rather not have users just willy nilly creating pivot tables against our OLTP databases. Is there a way to prevent these sort of connections?

The way our security works, when a user accesses the application, it checks the AD group, and if the user is in the group the application lets them in. We use the same AD group to allow access to the database, the application passes the user's information to SQL Server.

1
  • I'm afraid only Resource Governor can help if you can't touch permissions. You can throttle the boss's connection to not kill the CPU and mem, can't touch IO, though.
    – Marian
    Apr 16 '13 at 18:48
2

You can do that using LOGON TRIGGER.

something like below :

--Prevent acces from Excel
CREATE TRIGGER tr_stop_excel_users
ON ALL SERVER FOR LOGON
AS
BEGIN
IF APP_NAME() LIKE '%excel%'
 ROLLBACK
END

Refer to my answer here as it has the script to achieve it as well. You have the flexibility of customizing the script to check for Application, Users, etc and depending on what you want to allow or not, the table that governs the script can be modified.

1
  • 1
    Of course the application name is set by the client, so as long as you don't mind the fact that the users can trivially change the application name, it works great. Apr 17 '13 at 2:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.