December 23, 2008 at 9:54 am
My boss complained of the error "The database xxx is not accessible" from within SSMS when trying to expand the database. I was able to access the database. 4 of 5 user mappings to not show up in the User Mapping page for his login account. I verified that the user accounts exist in three of four of the databases. It appears one of the user accounts was dropped. I was able to re-map that database to the login and he is now able to access the database. The other three are still a problem. He can access the databases. They just don't show up in the User Mapping page.
I can see the principals via sys.Server_Principals & sys.Database_Principals. The SIDs between the users and login match. Remapping does not work; the check marks in the User Mapping page do not stay. I believe this is why: When I click the Script button on the User Mapping window, a dialog that states "There is no action to be scripted".
Other info:
SQL Server 2005 9.0.3257
windows Server 2003
EXEC sp_change_users_login 'Report'; --Returns no rows
EXEC sp_change_users_login 'Update_One', 'Domain\User', 'Domain\User'; -- Returns this error message: Terminating this procedure. The User name 'Domain\User' is absent or invalid. (I have since found that this proc is only for SQL Logins)
No messages in the SQL Server Logs or Event Logs
Has anyone every seen anything like this? I would like to understand how it happened, and how to fix it.
Thanks,
Paul O'Kasick
December 30, 2008 at 12:49 pm
PLAN A:
1. (DB Level) delete his account from the users in those THREE Databases that you have problem with
2. (Server Level) Re-Map for the login to the THREE DBs from the LOGin usermapping
If PLan A Not working, try PLAN B
1. (DB Level)Delete his account from ALL four DBs
2. (Server Level) Delete his Login from the server
3. (Server Level) Add his login back and do the usermappings
Hope this helps..
January 7, 2009 at 1:17 pm
Thanks for your reply. I was able to fix it by deleting the user accounts from the affected databases and recreating them via the User Mappings page.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply