Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Synonym Limitation Questions

Author  Topic 

ddamico
Yak Posting Veteran

76 Posts

Posted - 2013-09-20 : 16:46:25
Good afternoon.

I have come across and interesting issue with regards to the user of synonyms and was curious if anyone has knowledge of this.

I essentially have a database that has logging functions and stored procedures. Within a second database I have created sysnonyms so that I do not need to reference the three part name in my procedure. Example follows.

ETL Database
LogMessage (stored procedure)
GetEvent (function) get event associated to the message to log

BASE Database
LogMessage = Synonym of Etl.dbo.LogMessage
GetEvent = Synonym of Etl.dbo.GetEvent

The issue I am having is that after a third level of sub-procedures the LogMessage store procedure fails with the following message.
Report.Post_Report: At line 259: Report.[Post_Report_Sub1] : At line 92: Report.[Get_Sub2] : At line 1: Database 'ETL' does not exist. Make sure that the name is entered correctly.

However, the GetEvent function will still work at the last level just not the LogMessage procedure.

Curious if anyone else has seen this behaivour. Thanks.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-09-23 : 19:43:50
Are any of the intervening stored procedures being run in a different security context? This could be via an "EXECUTE AS" clause at create time or run time.

=================================================
The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen
Go to Top of Page
   

- Advertisement -