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.
| Author |
Topic |
|
MarkWB
Starting Member
8 Posts |
 Posted - 2008-10-16 : 11:01:05
|
| The add table window in the Query Editor of 2005 SSMS has 4 tabs that list objects in the target database: tables, views, functions and synonyms.All of them work except the synonyms tab - it will not list objects. I've googled this and the only relavent information I can find is this bug report opened more than a year after the release of the product:https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=282124&wa=wsignin1.0I am doing a migration of users from Access to SSMS. In Access, the users had a script that went out and linked a number of SQL Server tables from different databases. To make it easy for the users to transition, our solution is to create a SQL Server database that contains synonyms for the same tables that they previously had linked in Access. Unfortunately, since the Access "Design View" was the primary tool for the users, this solution isn't going to work if the SSMS Query Editor can't list synonyms.Does anyone know about the status of this or any workarounds? |
|
|
SimpleSQL
Yak Posting Veteran
85 Posts |
Posted - 2008-10-16 : 22:57:57
|
| Let me ensure I understand correctly. You created synonyms and they are not listed in SSMS?Can you please share output of sys.synonyms table? |
 |
|
|
MarkWB
Starting Member
8 Posts |
Posted - 2008-10-17 : 13:58:32
|
| SimpleSQL - no - I can see them in Object Explorer. sys.synonyms returns 44 rows. The problem is in the add table window of the query editor.Create a new query, then open the query editor. The add table window should open. If not, right click and choose "Add Table...". On the add table window, click the Synonyms tab. For me, nothing shows up here even though I have 44 synonyms. |
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2008-10-21 : 07:52:27
|
| You could create Views with old names users are familiar with which do a select from new tables. |
|
|
|
MarkWB
Starting Member
8 Posts |
Posted - 2008-10-21 : 10:10:01
|
| Thanks dardusky. Actually, that is on the list as one potential workaround. It is pretty unactractive though for a couple of reasons. One is that another goal of the project is to raise the bar on the user's capabilities - so we expect to eventually train them on creating and managing server objects (views, stored procedures, etc.) so they can re-use queries in their workgroups and create a library of components. So if we go with views instead of synonyms we have to come up with a naming convention that keeps the table abstracting views separate from the user views. We also have to come up with a security mechanism to keep them from modifying the table abstracting views. We could use schemas to keep them seperate, but then they have to explicitly qualify the special schema. That is not necessarily a problem, but it ties into the second issue which is that there are almost 200 users involved. So the number of queries that are going to be created will easily be in the thousands (that is all the J-SQL that we will port over initially plus whatever new code they create after the migration). So if we go with a workaround like using views and having special schemas, the problem is we end up having this solution hardcoded into a huge body of queries that we will be stuck with for years to come.So, yes, technically it works - but I guess we are hoping to find a way to get the software to work correctly. In the absence of that, we are looking for a solution that can be easily backed out of once MS releases a fix in a service pack. |
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2008-10-23 : 05:57:27
|
| Mark,to be honest I hadn't used Synonyms before but I replicated your problem and also found Microsoft is aware of the bug. On the View issue - I personally prefer to present users with Views rather than letting them know the table structure. This enhances security by hiding elements which they do not need to see. It also allows you to influence user queries. Eg limits impact of them writing Select * from .... if you have already limited number of columns in View and / or number of rows returned. Also you have the option of joining tables inside Views / indexed Views etc again optimising user queries.On the re-naming / altering issue - you can set SELECT permission only on the core Views so users will not be able alter anything. No need for seperate schema - just a seperate database role which has full permission on the core Views. Then use the role to find / maintain these core Views. View also allow you to change table structure with-out users having to change their queries as long as Views are altered to fit new structure. So Views give benefits of Synonyms but with additional advantages. |
|
|
|
|
|
|
|
|
Synonyms in SSMS Query Editor