Wednesday, May 23, 2012 5:05 PM
While attempting to publish a database using Database Publishing Wizard on SQL 2005, the database I want to use does not show in the Select Database window. This database was restored from a backup of an SQL 7 machine. I've verified that the userid logged into the server running DPW has full rights to the database (even though I don't see specific rights for that user for databases I CAN see).
Wednesday, May 23, 2012 6:43 PMModerator
It is just a suggestion. Try changing the compatibility level of the restored database to 80 or 90.
Hope this helps..
Friday, May 25, 2012 6:01 PM
The default level of security and visibility of data in SQL Server 2005 is much tighter that SQL Server 7.0 and 2000. By default, the only databases you will see are master,tempdb, and any database that is owned by your login. Having rights granted within the database is not enough.
Try this to see if it resolves your problem:
GRANT VIEW ANY DATABASE TO <login>;
That allows you to see databases that you do not own. As noted before, you may have rights to these databases.
Next, you may need rights to the database metadata if you do not have it. In that case, look into the VIEW DEFINITION and VIEW ANY DEFINITION permissions to see what is appropriate in your case.
- Marked As Answer by Maggie LuoMicrosoft Contingent Staff, Moderator Thursday, May 31, 2012 9:32 AM
Tuesday, August 28, 2012 11:46 PM
I'm running into the same problem as MGMaher.
I'm very new to using MS SQL server.
Where/how do I run that Query above?
Wednesday, August 29, 2012 7:05 PM
If you are using SQL Server Management Studio (SSMS) you connect to the server in question. If you have the Object Explorer on the left side, connect there. Otherwise create a query window, right click on it, and choose new connection. Then connect to the server in question.
Then, in the query window, type in the command. On the menu lines above, there should be an Execute button. Click on that.
All the best,