December 18, 2009 at 10:33 pm
1.I have taken a backup of some database 'A' from a server 'ServerA'.
2.I have restored the database 'A' on Server B. I have done this by creating the new database by the same name and run the restore and given filename of backup taken on my local hard drive
3.Create a user 'User A' and assigned this user as the db_owner of Database.
4. From my .Net Application i have given the connection string in web.config and application is working fine.
5. Now if i am trying to access the database by going through MAnagement Studio and after logging to my database i am clicking on database in Server Explorer to show database objects. It is not allowing me to open that. It is giving error as:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The server principal "jobuser" is not able to access the database "acad" under the current security context. (Microsoft SQL Server, Error: 916)
Here Jobuser is my database-A user. and acad is another database. I dont know why its referring to acad database.
This is happening for all other logins also. Everylogin created earlier was working fine. Now everylogin is referring some another database.and giving same error
Please help
December 20, 2009 at 4:29 pm
Is "acad" the default database for those users ?
December 20, 2009 at 6:04 pm
Did you create new logins when you restored the database on the new server and make sure those are mapped to users in the database?
December 20, 2009 at 9:34 pm
Yes i have created new logins
December 21, 2009 at 1:12 am
Are those logins correctly mapped to the users in the database?
If you just created new logins on the server it's highly unlikely that they won't be. Look up 'orphaned users' in Books Online for details.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 21, 2009 at 4:09 am
Check if the user you are using is an Orpahaned user ?
sp_change_users_login 'report'
December 21, 2009 at 9:26 am
To fix your orphan users:
EXEC sp_change_users_login 'Auto_Fix','YourUser'
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply