August 20, 2010 at 12:55 pm
Hi Perry,
Sure, the sp_change_users_login 'report' did not return any results.
Thanks!
Sandy
August 20, 2010 at 5:39 pm
Derrick Smith (8/20/2010)
when you try to auto_fix a db user that doesn't have a matching login created on the server. It's not a case of missing parameters as mentioned earlier.
When attempting to auto fix a database user you must supply NULL for the server login and a password. These were missing from the original query!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 23, 2010 at 8:19 am
I'm sorry I'm not sure what you mean Perry? I run that procedure every time I move a database to a new server so am familiar with the syntax, here's what I normally run: exec sp_change_users_login auto_fix, '<user name here>', NULL
Is this incorrect???? It has always worked for me in the past - this error is like nothing I've ever seen 🙁
Thanks again for your assistance!
Sandy
August 23, 2010 at 8:50 am
Hawkeye_DBA (8/23/2010)
here's what I normally run: exec sp_change_users_login 'auto_fix', '<user name here>', NULLIs this incorrect????
that would only work when the login already exists on the server. To auto fix and create a new login you need
exec sp_change_users_login auto_fix, '<user name here>', NULL, '<password here>'
To fix a database user to a login that already exists i use
exec sp_change_users_login 'Update_One', '<user name here>', '<Server login name here>'
You say that report showed no orphans, did you execute under the context of the database you are having issues with?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 24, 2010 at 9:32 am
Oh yes, definately did. The user is in the database and on the server. However, in the database the user shows Disabled, which it is not but in sysusers isenabled = false. And on the server the user is not showing as mapped to the database, which it clearly is.
The users owns a schema in the database and is a critical user for our application because that's how the app connects. It's working just fine, but SSMS just shows odd things!
I wish I could fix this without having to drop the user but I'm afraid that whatever is wrong in there will only be fixed by doing so.
I haven't had a chance to drop the user so I will see if I can get a maintenance window to do it tonight. At least everything is still working 🙂
Thanks again for your assistance Perry!
Sandy
August 24, 2010 at 2:48 pm
What version SQL server 2005 are you using?
Has this database been upgraded from a previous version of SQL Server?
Also,
Hawkeye_DBA (8/24/2010)
However, in the database the user shows Disabled, which it is not but in sysusers isenabled = false. And on the server the user is not showing as mapped to the database, which it clearly is.
OK, you really need to be using the following catalog view
select * from sys.database_principals
To view disabled logins check
select * from sys.server_principals
Hawkeye_DBA (8/24/2010)
I wish I could fix this without having to drop the user but I'm afraid that whatever is wrong in there will only be fixed by doing so.I haven't had a chance to drop the user so I will see if I can get a maintenance window to do it tonight. At least everything is still working 🙂
Thanks again for your assistance Perry!
Sandy
Don't be hasty, its working so do not drop the user yet!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 25, 2010 at 8:04 am
Hi Perry,
Thanks for following me on this! Ok, I didn't drop the user yet...because like you said, it's working! lol
Also, yes this was a restored 2000 database and then upgraded to 2005 SP3.
Here's what I got for the queries you suggested I run for the user, which I ran under the database context in question (sorry the formatting is funny when posting to forum).
select * from sys.database_principals
nameprincipal_idtypetype_descdefault_schema_namecreate_datemodify_dateowning_principal_idsidis_fixed_role
sde6SSQL_USERsde2005-04-27 16:04:11.6202010-08-20 10:49:52.167NULL0x93270AEBC0519044B7915ED41B44C4170
select * from sys.server_principals
nameprincipal_idsidtypetype_descis_disabledcreate_datemodify_datedefault_database_namedefault_language_namecredential_id
sde2630x93270AEBC0519044B7915ED41B44C417SSQL_LOGIN02010-04-26 08:03:38.8402010-08-20 09:35:35.710masterus_englishNULL
August 25, 2010 at 8:49 am
hmm, can't see anything wrong there. At this point i think i would be tempted to stop the ArcSDE services that use the sde user and do the following
1/ Drop the server login
2/ Re create the server login 'sde' with the current password (do not map to any databases though)
3/ execute the following
exec sp_change_users_login 'Update_One', 'sde', 'sde'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 30, 2010 at 8:01 am
Hi Perry,
Thanks for reviewing the query outputs. I know it is very odd.. hopefully by dropping and readding the user we will not see this issue again.
January 3, 2011 at 12:01 pm
Just to finish this old post up, I finally did drop and re-add the user which fixed the problem. Thanks for all of your help Perry!
January 3, 2011 at 5:47 pm
No problem, glad I could help.
BTW, happy year.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply