connect database user to security login user

After restore off a database the user under security logins was not created.
The user under Database and users was created, but that user MUST bee the
same user under security logins, if i try to make a new user with same name
under logins, thats ok, but i cant connect to the database becuse the user
allready exists, i cant delete the user under the database. What to do? Is
there som way to connect a database user to a security login user. Its SQL
security and login.
/Per W.sp_change_users_login.
Also, you can transport the logins with the correct sid, search KB for sp_he
lp_revlogin.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Per W." <pwbuf@.tiscali.no> wrote in message news:GdMOd.98909$Vf.3877273@.news000.worldonlin
e.dk...
> After restore off a database the user under security logins was not create
d. The user under
> Database and users was created, but that user MUST bee the same user under
security logins, if i
> try to make a new user with same name under logins, thats ok, but i cant c
onnect to the database
> becuse the user allready exists, i cant delete the user under the database
. What to do? Is there
> som way to connect a database user to a security login user. Its SQL secur
ity and login.
>
> /Per W.
>|||The following will list all users whose SPID is not mapped to a valid login:
sp_change_users_login 'Report'
"Per W." <pwbuf@.tiscali.no> wrote in message
news:GdMOd.98909$Vf.3877273@.news000.worldonline.dk...
> After restore off a database the user under security logins was not
created.
> The user under Database and users was created, but that user MUST bee the
> same user under security logins, if i try to make a new user with same
name
> under logins, thats ok, but i cant connect to the database becuse the user
> allready exists, i cant delete the user under the database. What to do? Is
> there som way to connect a database user to a security login user. Its SQL
> security and login.
>
> /Per W.
>

Conflict with logins/usres when Importing databases

Hi,

We have imported several databases from an old SQL Server 2000 deployment in a SQL Server 2005 instance. After the installations we have check that the import doesn't create the logins associated with the users nad now it seems impossible to create "manually" the login. What should be do? Re-import the database after having created the login?

Thanks
You should still be able to create the login using CREATE LOGIN and then run the system proc
sp_change_users_login to map the existing users to the newly created login.

Reimporting the database will probably not work as the SIDs of the new logins will not match those of the old logins.

HTH.
|||Effectively, we have been able to create the login but we are unable to associate it to the user using the the "User Properties". The field "Login" appears empty and disabled, so there is no way to assign a user from this screen.

Reading your answer I understand that I can map the users to the newly created logins using the sp_change_users_login function. It's true? Could you send me a sample of use of this function? How should we execute it? (We are SQL Server newbies coming from Oracle world......)+

Thanks
|||

I assume you imported only your user-database probably using import wizard or something. Security information (users and logins) is stored in 'master' database in SQL Server world. You have to transfer these information from your old database to new one.

Check this out.

How to transfer logins and passwords between instances of SQL Server

http://support.microsoft.com/kb/246133/en-us

|||sample:

sp_change_users_login 'AUTO_FIX', 'some_login'

|||Check the information in Books Online which will give you a full explanation of the options open to you:

http://msdn2.microsoft.com/en-us/library/ms174378.aspx

Essentially to map an existing user Bob to a new login of Bob you're looking at running:

sp_change_users_login 'Update_One', 'Bob', 'Bob';

Hope this helps;

Confirm password problem??

I am running SQL Server 2000 on a Windows 2000 Server. Whenever I change
anything on the properties of any of the logins, it asks me to confirm the
password even though it has not been changed. Actually, I can just view the
properties of any login, change nothing, click OK, and it will still ask me
to confirm the password. If I type in the password (and the password has
not been changed) it says it is incorrect. I must retype the password,
click OK, verify it, and then click OK again. What is the problem here?Sounds like this may be the issue you are running into:
FIX: You Are Prompted for Password Confirmation After You
Change a Standard SQL Server Login
http://support.microsoft.com/?kbid=826161
-Sue
On Thu, 8 Apr 2004 07:14:43 -0500, "Dave"
<DMikeK@.hotmail.com> wrote:

>I am running SQL Server 2000 on a Windows 2000 Server. Whenever I change
>anything on the properties of any of the logins, it asks me to confirm the
>password even though it has not been changed. Actually, I can just view th
e
>properties of any login, change nothing, click OK, and it will still ask me
>to confirm the password. If I type in the password (and the password has
>not been changed) it says it is incorrect. I must retype the password,
>click OK, verify it, and then click OK again. What is the problem here?
>|||Sue,
Thanks. I did try searching the Knowledge Base but didn't find that one,
for some reason. I'll give it a try.
Dave
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:6vja70leoufd0535ral22cb1q7f4hnatq0@.
4ax.com...
> Sounds like this may be the issue you are running into:
> FIX: You Are Prompted for Password Confirmation After You
> Change a Standard SQL Server Login
> http://support.microsoft.com/?kbid=826161
> -Sue
> On Thu, 8 Apr 2004 07:14:43 -0500, "Dave"
> <DMikeK@.hotmail.com> wrote:
>
the
the
me
>

Confirm password

Hi,
I have a login that has access to a certain database, when
i go to the logins session of EM to give the login acces
to another user, i map the user with the login in a new
database an i give him de db_owner role (Dev Host), when i
do the ok or aply, i'm prompt to confirm the password. For
me this is strange '? The login exists, i just want to
give the user access to another database.
Can anyone explain me this
Thanks in advance
Miguel CorreiaMaybe this will help...
http://support.microsoft.com/default.aspx?kbid=826161
Tim
>--Original Message--
>Hi,
>I have a login that has access to a certain database,
when
>i go to the logins session of EM to give the login acces
>to another user, i map the user with the login in a new
>database an i give him de db_owner role (Dev Host), when
i
>do the ok or aply, i'm prompt to confirm the password.
For
>me this is strange '? The login exists, i just want
to
>give the user access to another database.
>Can anyone explain me this
>Thanks in advance
>Miguel Correia
>.
>