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?
LVL 3
FMabeyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dqmqCommented:
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.  
dqmqCommented:
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.
FMabeyAuthor Commented:
And if we haven't got a copy of our old Master database?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

dqmqCommented:
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:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_23073982.html









 


FMabeyAuthor Commented:
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.'
dqmqCommented:
Try this, for example:

USE master
EXEC sp_addlogin 'STWO', '<password>'
EXEC sp_addalias 'STWO', '<user name>'

where '<user name> ' is the name of the corresponding user in the database security users list.  And do the same for 'STWO#'

Offhand, I don't know what the difference between STWO and STWO# is about, but if the application really creates two logins for each user, then you need to treat them similarly.

Also, then you may need to server grant permissions to the two logins just created.  I have no idea what permissions they need.  

Rembember, the error message you quoted, does NOT resound like a missing login.  It would occur AFTER the login, so you may need to dig a little deeper.

 



Then you need to grant per


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FMabeyAuthor Commented:
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.
dqmqCommented:
>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."

 :>)
FMabeyAuthor Commented:
They are indeed! Cheers for your help over the weekend... It pointed me in the right direction!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.