Problems with SQL Server 2000 logins
Hi all,
We have a problem with our SQL Server 2000 logins. We were updating the server that SQL Server sat on and it failed.... This meant a total reinstall of sql server and a restore of the databases off backup.
However, now we cannot use any of the logins... We still have a list of users under the database, but there are no users in the sercurity - logins section in Enterprise manager... We cannot login useing any of the logins... It gives an error:
SQL Error: Invalid Cursor - Halt Application
Any help?
We have a problem with our SQL Server 2000 logins. We were updating the server that SQL Server sat on and it failed.... This meant a total reinstall of sql server and a restore of the databases off backup.
However, now we cannot use any of the logins... We still have a list of users under the database, but there are no users in the sercurity - logins section in Enterprise manager... We cannot login useing any of the logins... It gives an error:
SQL Error: Invalid Cursor - Halt Application
Any help?
dqmq
You need to restore the logins (different than users, BTW) and their permissions. This operates at the server level, not the database level...so restoring a database is not sufficient. If you can restore your old master database somewhere, then you may be able to extract the logins and permissions from that and script them back into the new server.
One more thing. That error message is NOT what I would expect from a missing login problem. I don't know what to make of THAT.
ASKER
And if we haven't got a copy of our old Master database?
If you have no other source for logins, then you need to add them back in manually. Yech...
You can logon as SA and enter the logins manually through the GUI or you can write a script. Check this out:
https://www.experts-exchange.com/questions/23073982/Restore-Database-on-different-server.html
You can logon as SA and enter the logins manually through the GUI or you can write a script. Check this out:
https://www.experts-exchange.com/questions/23073982/Restore-Database-on-different-server.html
ASKER
Hmmm... I thought you might say that! I'd already found that article and tried it.
I link the user name to the login but I still get the same error when I login.
When I create a user using the application that uses the database it creates one user name (e.g. STWO). It also creates 2 server logins STWO and STWO#.
I remember that when I delete a user I have to use this code:
sp_dropalias STWO#
sp_droplogin STWO
Does this mean anything to you?
I have tried to use sp_addalias 'STWO#', 'STWO'
It tells me 'User or role '\STWO#' already exists in the current database.'
I link the user name to the login but I still get the same error when I login.
When I create a user using the application that uses the database it creates one user name (e.g. STWO). It also creates 2 server logins STWO and STWO#.
I remember that when I delete a user I have to use this code:
sp_dropalias STWO#
sp_droplogin STWO
Does this mean anything to you?
I have tried to use sp_addalias 'STWO#', 'STWO'
It tells me 'User or role '\STWO#' already exists in the current database.'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This was not the exact solution I used as our problem ended up being a software issue... However, 9 times out of 10, this would have been sufficient.
>This was not the exact solution I used as our problem ended up being a software issue...
So, really, the points are more deserved by my offhand comment:
"One more thing. That error message is NOT what I would expect from a missing login problem. I don't know what to make of THAT."
:>)
So, really, the points are more deserved by my offhand comment:
"One more thing. That error message is NOT what I would expect from a missing login problem. I don't know what to make of THAT."
:>)
ASKER
They are indeed! Cheers for your help over the weekend... It pointed me in the right direction!