June 24, 2010 at 9:20 am
Help!
Here's the situation:
Db was restored from backup 2000 to 2005
Logins were recreated for 2005
Users were re-mapped to logins
Unfortunately a Login was granted sysadmin and should not have been. I removed sysadmin from Login.
Login is a User in db, however, the user is Disabled. I run ALTER LOGIN <username> ENABLE, nothing happens!
I cannot assign Login to User until the User is Enabled on the DB and I cannot drop the login - it is critical to operation.
Even stranger.. after I ran the Enable Login I was able to login with the login/user to that db and worked fine!
Help!
Thanks,
Hawkeye
June 24, 2010 at 9:24 am
Never mind - this did NOT work.
I was able to Enable the user, but I still cannot map the login to the db at the server-level.
Help would be GREATLY appreciateD!!
:hehe:
June 24, 2010 at 9:58 am
The login is not displaying correctly... helplogins shows it mapped to the database user. I have closed and reopened SSMS 2005 several times now. 🙁
August 19, 2010 at 10:57 am
Issue fixed.
August 19, 2010 at 11:53 am
exec sp_change_users_login 'report'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 20, 2010 at 8:04 am
Hi Perry,
I'm not sure what you mean by this?
I actually thought I had fixed this.. by looking at it today in SSMS 2005 I see that the checkmark beside the database name is STILL not persisting after the Login Properties window is closed. This is very bizarre.
Here's what I get if I try to run EXEC sp_change_users_login Auto_Fix, 'username'
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207
An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.
Something is wrong with this database 🙁
August 20, 2010 at 8:20 am
Hawkeye_DBA (8/20/2010)
try to run EXEC sp_change_users_login Auto_Fix, 'username'
that's because you have some parameters missing. if autofixing the database user account you need to supply a password and null for the server login. you should use
EXEC sp_change_users_login 'Auto_Fix', 'username', NULL, 'PAssw0rd1'
As i said above run the report option first and let me know the output
EXEC sp_change_users_login 'Report'
Hawkeye_DBA (8/20/2010)
Something is wrong with this database 🙁
There is nothing wrong with your database, this can be easily repaired 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 20, 2010 at 8:41 am
Thanks for your help! Ok,
I ran the report and it showed two other logins that were no longer registered on the server so I dropped them from the database after checking that they are indeed not used.
Unfortunately neither of these was the login in question 🙁
I ran auto_fix and still nothing...I'm baffled and bewildered! It also shows the login on the database as Disabled, but clearly it is not! I can login to the server and see all of the db objects it owns.
August 20, 2010 at 9:21 am
could you post the output of the following queries when run against your database
select distinct sp.name, sp.sid from sys.server_principals sp, sys.database_principals dp
where sp.type = 'S'
and not exists (select sid from sys.database_principals dp2 where dp2.sid = sp.sid)
select name, sid from sys.database_principals
where type = 'S'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 20, 2010 at 9:36 am
Here's the output from the context under the database - I'm soo sorry, too much multi-tasking on my end:
namesid
networks010xC2E15FFC2A12E24580EF2521D8BF3D94
gbagis_user0xC70796241EF53D4980E4A24429B629F2
GBA_USER0x66B0FF5767E31B469499BB7BE97B0B68
RemoteAlerts0x1DFC31A017ACC84FA870987E91F56F24
networks0x94E92A14CAEC434D9088C93EBD8206D5
---------------------------------------------------------------------
namesid
dbo0x01
guest0x00
INFORMATION_SCHEMANULL
sysNULL
WFS_Editor0x426A4D5661452544854670B3A6B36889
sde0x93270AEBC0519044B7915ED41B44C417
gis_editor0xDF71297A7C4DEC46BAD1FF929E4CB04A
gis_viewer0xE9AE3EDB82B04C4BA6347D5B3ADBE2EC
gbagis_editor0x471D4616C15F6146A533DA54142CCE94
August 20, 2010 at 9:44 am
Sure!
Here's the first output:
namesid
sde 0x93270AEBC0519044B7915ED41B44C417
gis_viewer0xE9AE3EDB82B04C4BA6347D5B3ADBE2EC
networks010xC2E15FFC2A12E24580EF2521D8BF3D94
GIS_Editor0xDF71297A7C4DEC46BAD1FF929E4CB04A
gbagis_user0xC70796241EF53D4980E4A24429B629F2
GBA_USER0x66B0FF5767E31B469499BB7BE97B0B68
RemoteAlerts0x1DFC31A017ACC84FA870987E91F56F24
networks 0x94E92A14CAEC434D9088C93EBD8206D5
WFS_Editor0x426A4D5661452544854670B3A6B36889
gbagis_editor0x471D4616C15F6146A533DA54142CCE94
Second Output:
namesid
dbo0x01
guest0x00
INFORMATION_SCHEMANULL
sysNULL
August 20, 2010 at 10:37 am
Hmm, did you execute these queries under the context of your problem database?
The second query returns only the default database user accounts (as you would see in the master or model database)
Re run the queries and set the focus to the database you are having the issue with
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 20, 2010 at 10:48 am
Since this is a critical user, and I cannot afford to drop it during working hours.. I will do this tonight:
- change the users schema owner from the user to dbo
- drop the user
- re-add the user
- re-assign the schema to the user as the owner
I hope this will fix the problem, otherwise I'm completely stumped. It even indicates in sys.users for the db that this user does not have db access, when clearly it does.
August 20, 2010 at 11:09 am
sorry, I cut off the last query, here's the results:
namesid
networks010xC2E15FFC2A12E24580EF2521D8BF3D94
gbagis_user0xC70796241EF53D4980E4A24429B629F2
GBA_USER0x66B0FF5767E31B469499BB7BE97B0B68
RemoteAlerts0x1DFC31A017ACC84FA870987E91F56F24
networks0x94E92A14CAEC434D9088C93EBD8206D5
namesid
dbo0x01
guest0x00
INFORMATION_SCHEMANULL
sysNULL
WFS_Editor0x426A4D5661452544854670B3A6B36889
sde0x93270AEBC0519044B7915ED41B44C417
gis_editor0xDF71297A7C4DEC46BAD1FF929E4CB04A
gis_viewer0xE9AE3EDB82B04C4BA6347D5B3ADBE2EC
gbagis_editor0x471D4616C15F6146A533DA54142CCE94
August 20, 2010 at 12:05 pm
Hawkeye_DBA (8/20/2010)
Hi Perry,Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207
An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.
Just wanted to mention that this error shows up 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.
Can you run exec sp_change_users_login 'report' and paste the output here? I don't think that has been posted yet...apologies if you already have.
edit: typo
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply