June 10, 2009 at 2:52 pm
We have production and test SQL 2005 servers with a third party application that creates databases within the app. I have locked down the servers so that the app login has dbcreator role, via connection strings, and everything works for the app. Now the user comes to me and says while everything works fine on production, they cannot log into all of the test databases. I researched it a bit, and noticed that on test when I go into "Login Properties", in the User Mapping sometimes the user is "dbo" and the default schema is "dbo" - which is the same as production - however sometimes the user is the database username instead. This seems to line up with which databases are inaccessible for the user.
Digging around on the 'net, I see mostly things pointing to trying to run sp_change_users_login. However when I run the 'REPORT' version of that sp, the one that shows is dbo. Additionally, I tried to manually change the user in the properties tab, and I get Rename failed error 15023 ("User, group, or role "dbo" already exists in the current database").
Any easy answers?
Thanks!
Matt
June 12, 2009 at 8:18 am
I assume you have deleted the user acct in TEST and rescripted the login in PROD and reapplied it in TEST with the same results?
June 12, 2009 at 8:26 am
I cannot remove the login from the test box as it is set up properly for perhaps half of the databases and it is a fairly active test box. I should be able to get an outage and try that, though.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply