How do you associate a user to a Login in SQL 2005

[rsyCoder]

I have DB that was restored from SQL 2000 into an instance of 2005. With the different Login/User structure I had to create a login to connect to the DB through ODBC.

What I am looking to do now is set it up so that the old user credentials can be used to login to the new DB and get all the same permissions.

So in old 2000 DB user name is john and password is fortheloveofallthingsholypleaseletmein1.

If I create the Login john I get an error back stating that there is already an object named john. I am guessing that when it tries to make he user for the DB with the same name as the login that is being created it sees the current one and declines.

Maybe I have this wrong and the Login has nothing to do with connecting to the DB from ODBC, I just know that when I try to connect with john and fortheloveofallthingsholypleaseletmein1 I get back "Login filed for user 'john'" and the log shows state 5 (invalid userid). But when I created a login (johnlogin) for the server it created a user (johnlogin) for the DB that I was able to use to make a connection.

How do I make a login john and have it associate with user john that already has the permissions that are needed and not have to change the password so that it won't need to be changed on a lot of machines and in other apps that are using it?

Thanks for your help!

[NeoPa]

You don't say, but I'm getting that you are not seeing the existing user ID John when you look in the list. Is that right?

[rsyCoder]

Correct, after restore the DB, we will call it basedata, has all the old users, including john.

However there are no logins for any of the old users, including john.

If I make a new login then it will make a new user of the same name and those work fine for connecting and everything is shiny.

Is there a process I can use to make a newjohn get all permissions and table ownerships of john, then kill john, bury the body, and recreate john and pass the securables and ownerships back to the remade john from newjohn? Or will that create other issues that I am not seeing? Maybe just rename john to oldjohn and make a new john and move permissions from oldjohn to new john?

Thanks for your help.

[NeoPa]

I'm not sure how you could do much with the old John Login if it doesn't get displayed anywhere. Rename or delete would both require you to have access to it in the first place which, from my understanding, is what you don't have and which is the fundamental cause of your issues.

For the record, on my SQL 2005 server the Logins are stored under the server as :
{Server} / Security / Logins

Both the SQL Server and your local PC need to be members of the same security domain.

[Jerry Winston]

I think I might have a solution for you. If you take a look at the script SSMS attempts to execute to create the login John, it contains both a CREATE LOGIN statement as well as a CREATE USER statement when you choose 'map to database'. The trick is to create the login john without mapping it to your restored database. Once the unmapped login John is created, you can connect it to database users using the following code.

USE [basedata]
ALTER USER john WITH LOGIN = john
 

Although the code looks very straightforward, you won't be able to run this on all SQL2005 servers. This will work only for those with SP2+ installed.