SQL 2005 DBs and Logins migrate to SQL 2008R2 -> Transferred Logins do not show database in Object Explorer

Question

Hi Everyone,

I have transferred some DBs and logins from an SQL2005 server to a new SQL2008 R2 server. I used the backup and restore method along with the with the login transfer process described in KB918992. The default DB, login permissions, schemas and login to user mappings seem to look OK. 

Freshly created logins and DBs work correctly in Manaagement studio.

Transferred logins and DBs do not show any user DBs in the Object explorer pane.  If I do "Use DatabaseName" then I can query the DB fine but it still does not show in the object explorer pane. I realise this must be some sort of permission issue but dont really know where to start. Can anyone offer any advice?

Answer 1

Hi Krobar,

Have you been able to resolve this issue yet?

What level of access do you have on the server currently?  Also can you check to see who the current owner of the recently transfered databases are?  Changing the owner of the databases may resolve this issue if you currenlty have limited access.

Answer 2

Hi James,

 

Sorry for the very late reply.

For users who have not got lots of objects under the existing username I can fix this by dropping the username associated with the login and using the changedbowner script to change the owner to the login. For DBs where the current user own various objects though this is not really an option, do know of a potential fix or where to start in finding the cause of this problem?

Answer 3

Perhaps when you transferred the logins, you did not set the option to keep the same SID. If so then under the hood the database users are not mapped to the login even though they look like they are. The database users are orphaned, and doing what you did is how to fix it.

But if you keep the original SIDS from one server to another, and then bring over the database, the users in the database will be hooked up to the new logins.

Answer 4

check out the below link.

http://www.idevelopment.info/data/SQLServer/DBA_tips/Database_Administration/DBA_20.shtml