Home / server / Questions / 251845
Accepted
Gabriel Guimarães
Asked: 2011-03-26 06:41:16 +0800 CST

Block SQL 2008 R2 Insert Access on anything for user in MS ACCESS on AD Group

  • 772

Hi I would like to block access to insert any data on SQL when a User from a determined Active Directory Group access SQL from MS ACCESS.

Is this possible? if not is there any alternative?

I've tried a server trigger, and was able to prevent access to sql from someone by its IP, and the program name. But I would like to prevent inserts, is there a way to do this?

thanks, Gabriel

sql-server microsoft-access security permissions sql-server-2008-r2

1 Answers

  1. Best Answer

    You can block for an AD user or group. But not reliably by client used.

    Note: Permissions are for principals, not for code generally.

    The client name can determined by sys.sysprocesses.program_name.

    BUT

    This is set by Application Name in the connection string, so it can be any value. And you can't set it in MS Access (upto version 2003 at least): it shows as "Microsoft Office 2003" same as, say, Excel (you can set it from Excel oddly).

    So, no, you can't do it.

    • 1