Take the 2-minute tour ×
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.

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.

share|improve this question
    
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
add comment

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Well you can't rollback a big select/pivot statement. You can kill the session, but the boss won't be happy. –  Marian Apr 16 '13 at 18:47
    
I would leave that to OP as a decision .. –  Kin Apr 16 '13 at 18:50
    
well it was my boss that was inquiring about this, he fould the permission hole. I don't want users connecting to my oltp databases unless it's through an application. –  DForck42 Apr 16 '13 at 19:37
    
Also, @kin, power pivot shows up with the application name "Microsoft Office 2010". I set it up to block anythign with "Office" and it seems to work. Testing it out with my boss now. –  DForck42 Apr 16 '13 at 19:38
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. –  StrayCatDBA Apr 17 '13 at 2:17
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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