Sql-server – Is it possible to limit the scope of a SYNONYM in SQL Server

linked-serversql serversynonyms

I have a series of scheduled stored procedures that need to be run against different databases that have identical schemas (think multiple instances of an application). So for instance, every database will always have the table member which will always have the columns member_number, name, and dob. The stored procedure find_new_members() would use exactly the same SQL no matter which database it was executed against.

I've found the command CREATE SYNONYM which looks like it will do the trick if I do something like this:

DECLARE @database NVARCHAR(16) = 'ClientA';
DECLARE @statement NVARCHAR(MAX) = 'CREATE SYNONYM CurrentDB FOR ' + @database;
EXECUTE sp_executesql @statement;
// Run generic statements

This allows me to farm the generic SQL out to a stored proc, and then just pass in the target database name. The problem is, it looks like the SYNONYM is scoped at a database level, so this means I can't have an SSIS package that runs the scripts against each client in their own stream. Not without the SYNONYM changing and affecting all scripts.

Is it possible to scope a SYNONYM statement to a user-level or (ideally) a PID-level, so that I can at least kick off multiple jobs at once, each using a different value for the SYNONYM CurrentDB?

EDIT: To explain my use case a little more, we are running queries from a database we have full read-write-execute on, against multiple linked databases we only have read rights on. Those databases are snapshots of source systems that we have no control over, and are simply databases sitting in a data lake. If we start throwing around `sp_executesql statements on the master scripts, we're going to lose a lot of refactoring and code control aspects in our IDEs. Ideally, we're looking at "aliasing" a linked database at a PID level.

Best Answer

Just switch the database context.

A handy way to do this is to call sp_executesql in the target database:

EG, with a static reference to the target database:

exec ClientA.sys.sp_executesql @job;

or dynamically:

declare @dbname sysname = 'ClientA';

declare @job nvarchar(max) = 'select db_name()';

declare @sql nvarchar(max) = concat('exec ', quotename(@dbname), '.sys.sp_executesql @job;');

--print @sql
exec sp_executesql @sql, N'@job nvarchar(max)', @job = @job;
Related Question