August 8, 2013 at 10:28 am
SQL Server 2012 Enterprise
We have a database table/view that we want users to be able to select from, but we don't want them to be able to JOIN this object to other objects in a SQL statement.
Any ideas on how to accomplish this? Custom error code triggered through a database level trigger? I don't see how this is possible using a DML, DDL or logon trigger.
Evaluate SQL statements using extended events and then trigger a custom error code if needed? Is that possible?
August 8, 2013 at 10:50 am
I assume they need access to these other tables too, or you could restrict access to the tables you don't want them joining to.
You could use a stored procedure. This assumes they cannot create tables to insert the results to and then join. The downside, you would have to parameterize any filtering they might do and standardize the result set (unless you make it more complicated with dynamic sql). I'm not aware of another way.
Triggers are for insert, update, delete.
August 9, 2013 at 1:04 pm
I cannot imagine the sense of this requirement.
Maybe you could deny the select permission to all tables and grant the select permission on the one that should be selected.
Depending on the reason for this special requirement I could even imagine to copy this one table to a separate, new database. Then let the users have the select permission on this new database and NO permission on no table in the original database.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply