2

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.

e_i_pi
  • 217
  • 4
  • 12

2 Answers2

0

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;
David Browne - Microsoft
  • 49,000
  • 3
  • 53
  • 102
0

you can kindof scope different synonyms to users by using schemas. It does have the caveat that all queries, new and existing, should be properly qualified with schema.

Create a schema for each user and make that schema the default for the respective user

create user user1 for login user1;
create user user2 for login user2
go

create schema user1 authorization user1;
go
create schema user2 authorization user2;
go


ALTER USER [user1] WITH DEFAULT_SCHEMA=[user1]
ALTER USER [user2] WITH DEFAULT_SCHEMA=[user2]

next, create synonyms for the datbase that will match the user you would like to access it. in example, database1 will be accessed by user1.

create synonym user1.members for linkedserver.database1.dbo.members
create synonym user2.members for linkedserver.database2.dbo.members

If you do not specify schema in your query the default schema will be the used, so your query can be:

SELECT * FROM members

If user1 is running it, it will use user1.members, which goes to database1 and user2 will go to database2.

Bob Klimes
  • 3,400
  • 1
  • 19
  • 31