Our company has a bunch of legacy Access databases. 99% of what they do is connect to SQL Server, but the end-users are extremely married to the Access interface and completely unwilling to switch to ReportServices.

My problem is the security. The connections are all being made with connection strings that have username/password combos. The Access developer has figured out that she can go to SSMS and log directly in as those username/password combos and then have those permissions in the production server and she is abusing it. We've talked to her about it and she continues to do it anyway. The company won't reprimand her because they won't fire her (LONG story).

What can I do? I haven't pissed around with Access in over a decade. Is there any way to provide the connection they need without the username/password being known?

If I understand this correctly, you only want the Access developer, to access SQL Server through Access and through SSMS.

You can set up a DDL trigger which throws here out if app_name() does not return whatever is returned for Access. But she can easily fake that in the login form to SSMS. Of course, that could be construed as a workplace violation, but it seems that you are a loser in the political game.

Then there is more heavy-duty solution: put Access on Terminal Server, segment the network so that she cannot access SQL Server from her desktop. This is a common advice for two-tier applications. But in this case, a developer and a lot of people who are lovers of Access? You know that better than me, but from what you said, I don't expect any success.

I think the best you can do is to review what permissions she needs. I know nada of Access, but reasonably she would need db_datareader/writer and maybe EXEC on the dbo schema. If she need anything beyond that, she will need to ask you to perform those actions.

Good news! Read this article on my web site. It includes two examples how you can package the permissions to start a job in a stored procedure. Implement that, and you can drop a lot of her permissions.http://www.sommarskog.se/grantperm.html

The job is only meant to be run manually. There are steps that have to be done on someone else's end creating and putting files in place, etc. It's archaic and dumb, but it's what they know and it's how they think they want it.

robin.pryor (5/9/2014)The job is only meant to be run manually. There are steps that have to be done on someone else's end creating and putting files in place, etc. It's archaic and dumb, but it's what they know and it's how they think they want it.

fair enough Robin...doesn't sound like you are going to win this easily.

you mentioned "abusing permissions" .....is this from your perspective or a real corporate issue that could potentially damage the company/data integrity....if the later then I fear you will have to go to war

______________________________________________________________you can lead a user to data....but you cannot make them think and remember....every day is a school day

I second Erland's approach: create a sproc that will perform the tasks that need to be done and use one of the options described in the article he referenced.We do the very same to let a user with low privileges start a SQL Agent Job.As a side note: It doesn't matter if she has the permission to do what that SQL Agent Job does since this is done using the privileges being used to call the Job (either the Windows account that's running sql agent or the proxy being used).