Category: DataBase

dbo vs. db_owner In every database there is a special user, dbo. While dbo has the effective permissions of a database owner, it is not one and the same as the db_owner role. Any user account can be assigned to the db_owner role, giving that user complete control of the database. The dbo user account is a special account which all members of the sysadmin role are implicitly mapped to. Also, the system table sysdatabases (located in master) stores who is the owner of the database (and is assigned the dbo user account) in the sid field. So not only […]

Refer to the below resources for gret in-details technical write ups: How to move databases between computers that are running SQL Server How to transfer logins and passwords between instances of SQL Server PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete Managing Metadata When Making a Database Available on Another Server Instance (SQL 2008)

Run below query as the user for whom you would like to know all the accessible DBs. Query: SELECT [Name] as DatabaseName from master.dbo.sysdatabases WHERE ISNULL(HAS_DBACCESS([Name]),0)=1 ORDER BY [Name] Results: DatabaseName master model msdb tempdb           .