asked
Cross DB Query Prevention for Specific DB's
Hi Guys I have a bit of a problem. I have set up snapshot replication to copy a reporting database up to a server. When the agent job executes it fires a stored procedure that adds all objects that are not currently replicated to be published. My problem is that when I gave this to our report writers to test, they broke it immediately by writing a view that references a database that is not on the destination server. Is there a way of preventing a report writer from accessing certain DB's(or only allowing access to) from within another DB? The problem is they still need access to all the other DB's for other work. Or maybe there may be something in the replication process that could help? Thanks in advance
Ok. I think there is a work around. Since you are using a customized procedure to add objects that are not currently replicated to be published, you can filter out the objects accessing other databases by appending something like AND EXISTS( SELECT OBJECT_NAME (referencing_id),referenced_database_name, referenced_schema_name, referenced_entity_name FROM sys.sql_expression_dependencies WHERE referenced_database_name = DB_NAME() ) Or you can tweak it according to your customized procedure. One limitation is that you need to be using SQL 2008+. But if you are on other SQL 2005, then you may need to search the text of the objects against all the databases/schemas like "`% DATABASENAME.SCHEMANAME.%`", "`% [DATABASENAME].SCHEMANAME.%`", "`% DATABASENAME.[SCHEMANAME].%`", "`% [DATABASENAME].[SCHEMANAME].%`" which is not a pretty solution as there is a probability of deriving false information like commented code. But in your case there could be only few objects, so you can visit them by storing the culprit object names in a table. If you still have some ambiguities about how to search (if you are using SQL 2005), then let me know and I will try to help you accordingly.
The only thing that comes to mind is to use a different active directory group to provide access to this database that doesn't have access to the others. I'm not even sure that would work.
9 People are following this question.
