Adding Logons

I have transfered databases from one server to another.
All my users are showing up in my databases but there is nothing in the
"Login Name" column. I tried adding in the user in EM in Logins under the
Security folder and that works also. But when I create the user and give it
access to the database, I get an error that says the user already exists.
Now the user is in the Logins and Users section but still no Login Name
showing and you can't logon to the database. But when I drop the user from
the database and add it to the Login User again, it works fine. Do I have
to do this for each user? This is a problem as we have about 15 databases
and about 40 users.
Is there a better way to deal with this?
Thanks,
TomOn Apr 19, 6:59 pm, "tshad" <t...@.home.com> wrote:
> I have transfered databases from one server to another.
> All my users are showing up in my databases but there is nothing in the
> "Login Name" column. I tried adding in the user in EM in Logins under the
> Security folder and that works also. But when I create the user and give it
> access to the database, I get an error that says the user already exists.
> Now the user is in the Logins and Users section but still no Login Name
> showing and you can't logon to the database. But when I drop the user from
> the database and add it to the Login User again, it works fine. Do I have
> to do this for each user? This is a problem as we have about 15 databases
> and about 40 users.
> Is there a better way to deal with this?
> Thanks,
> Tom
You must have restored the database to new server. The reason you're
having this problem is the SID for login and SID for the user in the
database are different. You have Orphaned Users. Since you have the
login in the server and user in the database you can keep doing what
you're doing remove the user and add the user again. Or you can use
sp_change_users_login. Check the books online for detailed usage of
the system stored proc or you can read the article for how to fix your
problem. Look into Chadmat's last post that has the tsql for
sp_fixusers procedure.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12615
Good day,
Bulent|||"Bulent" <bulentgucuk@.gmail.com> wrote in message
news:1177032504.814702.279020@.n76g2000hsh.googlegroups.com...
> On Apr 19, 6:59 pm, "tshad" <t...@.home.com> wrote:
>> I have transfered databases from one server to another.
>> All my users are showing up in my databases but there is nothing in the
>> "Login Name" column. I tried adding in the user in EM in Logins under
>> the
>> Security folder and that works also. But when I create the user and give
>> it
>> access to the database, I get an error that says the user already exists.
>> Now the user is in the Logins and Users section but still no Login Name
>> showing and you can't logon to the database. But when I drop the user
>> from
>> the database and add it to the Login User again, it works fine. Do I
>> have
>> to do this for each user? This is a problem as we have about 15
>> databases
>> and about 40 users.
>> Is there a better way to deal with this?
>> Thanks,
>> Tom
> You must have restored the database to new server. The reason you're
> having this problem is the SID for login and SID for the user in the
> database are different. You have Orphaned Users. Since you have the
> login in the server and user in the database you can keep doing what
> you're doing remove the user and add the user again. Or you can use
> sp_change_users_login. Check the books online for detailed usage of
> the system stored proc or you can read the article for how to fix your
> problem. Look into Chadmat's last post that has the tsql for
> sp_fixusers procedure.
> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12615
Yes, I did use a new server.
I just installed Sql Server on the new machine (Jupiter). I backed up the
couple of databases I have on my old Server (Venus) and then restored them
on Jupiter.
Can I just backup Master from Venus and restore it to Jupiter. I know that
Master keeps the Logins in its tables. Would this work or would it cause
other problems?
Thanks,
Tom
> Good day,
> Bulent
>|||"tshad" <t@.home.com> wrote in message
news:%23WQxfu2gHHA.2368@.TK2MSFTNGP04.phx.gbl...
> "Bulent" <bulentgucuk@.gmail.com> wrote in message
> news:1177032504.814702.279020@.n76g2000hsh.googlegroups.com...
>> On Apr 19, 6:59 pm, "tshad" <t...@.home.com> wrote:
>> I have transfered databases from one server to another.
>> All my users are showing up in my databases but there is nothing in the
>> "Login Name" column. I tried adding in the user in EM in Logins under
>> the
>> Security folder and that works also. But when I create the user and
>> give it
>> access to the database, I get an error that says the user already
>> exists.
>> Now the user is in the Logins and Users section but still no Login Name
>> showing and you can't logon to the database. But when I drop the user
>> from
>> the database and add it to the Login User again, it works fine. Do I
>> have
>> to do this for each user? This is a problem as we have about 15
>> databases
>> and about 40 users.
>> Is there a better way to deal with this?
>> Thanks,
>> Tom
>> You must have restored the database to new server. The reason you're
>> having this problem is the SID for login and SID for the user in the
>> database are different. You have Orphaned Users. Since you have the
>> login in the server and user in the database you can keep doing what
>> you're doing remove the user and add the user again. Or you can use
>> sp_change_users_login. Check the books online for detailed usage of
>> the system stored proc or you can read the article for how to fix your
>> problem. Look into Chadmat's last post that has the tsql for
>> sp_fixusers procedure.
>> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12615
This SP did work great, BTW.
But I am still curious about the Master and whether I can or even need to
move it to the new server. As well as what problems I could run into such
as incompatabilities (assuming that the new Sql Server was installed exactly
the same as the old one).
Thanks,
Tom
> Yes, I did use a new server.
> I just installed Sql Server on the new machine (Jupiter). I backed up the
> couple of databases I have on my old Server (Venus) and then restored them
> on Jupiter.
> Can I just backup Master from Venus and restore it to Jupiter. I know
> that Master keeps the Logins in its tables. Would this work or would it
> cause other problems?
> Thanks,
> Tom
>> Good day,
>> Bulent
>