CODEWITHSUNDEEP
databasesql-server-2008stored-proceduressynonym
Lakedaimon
Lakedaimon

Reputation: 1934

SQL Server: problem with synonym per user created by a stored procedure

I've got several stored procedures (worker procs) which will be queried by a main stored proc called Calc. Calc creates a synonym called S of a table, which name is passed by a parameter.

The worker procs work with the synonym table.

Works for admin login.

Problem: If a user executes Calc the synonym will be Domain/Username.S and the worker which access S doesn't find Domain/username.S

If the admin created the synonym it is called dbo.S and workers work by only accessing S.

How can the worker stored procs access the synonym created by the user which called Calc? So that X users can call Calc and operate on different tables S but use the same worker procs.

Thank you in advance, hopefully somebody can help me.

Environment: SQL Server 2008 Granted User Rights: Create Synonym

Upvotes: 0

Views: 667

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280350

Use the schema prefix when creating calc, executing calc, creating S, and calling S. Or make sure that all of your users have access to the same default schema. But I prefer the former.

Upvotes: 1

Related Questions