Friday, March 30, 2012

Is there a SQL Server equivalent for Oracle synonyms?

I have a SQL Server database which has one user (UserA) which owns some tables. I've added an additional user (UserB) to the database such that it has access to the tables owned by UserA. What is happening is that when I log on as UserB I have to fully qualify table names and fields in my SQL statements when I deal with tables owned by UserA. Is there a way make the tables accessible without specifying the owner? In Oracle you could create a public synonym for the table eg. <table_name>. Wherever that synonym is referenced the DBMS would know thats its refering to UserA.<table_name>. Is such functionality available in SQL Server? Thanks.I found out that if the tables belong to 'dbo' they can be accessed without the fully qualified names.|||Specifying schema owner is a good practice. It releaves the optimizer to do it EVERY time you choose to be lazy about it.

No comments:

Post a Comment