I'm always having problems when I want to move a SQL database to another SQL
Server. The way I do it isl iek this: I make a backup of the database on the
original server, copy it to thje new server, make there a new database, and
restore the backup in that new database.
So far no problem, but when I want to put put the logins in it I'm getting
trouble:
I want to make on the new server a Login with the same name and password: so
I go to the Security\Logins\, and create on. But when I want to assign that
Login to the SPecific Database (on the Database Access-tab), I got an error
which is saying that that there is alreaddy a user or role with that name
for that database.
When I go look in the Databases\MyDataBase\Users\ there isn't any user in
it...
Anybody knows how to resolve this very annoying problem? Is there a way to
backup/copy the Logins and Users?
Maybe I do something wrong when creating my users?
I create them like this: I go to the Security\Logins\ and assign them there
to the database (on the Database Access-tab). Than I go to the
Databases\MyDataBase\Users\ and add them there: New -> Database User...
Anybody got any idea? I woudl really appreciate some help here.
Thanks a lot,
Pieterhi Pieter,
"DraguVaso" <pietercoucke@.hotmail.com> ha scritto nel messaggio
news:4007da7e$0$311$ba620e4c@.news.skynet.be...
quote:
> Hi,
> I'm always having problems when I want to move a SQL database to another
SQL
quote:
> Server. The way I do it isl iek this: I make a backup of the database on
the
quote:
> original server, copy it to thje new server, make there a new database,
and
quote:
> restore the backup in that new database.
> So far no problem, but when I want to put put the logins in it I'm getting
> trouble:
> I want to make on the new server a Login with the same name and password:
so
quote:
> I go to the Security\Logins\, and create on. But when I want to assign
that
quote:
> Login to the SPecific Database (on the Database Access-tab), I got an
error
quote:
> which is saying that that there is alreaddy a user or role with that name
> for that database.
> When I go look in the Databases\MyDataBase\Users\ there isn't any user in
> it...
> Anybody knows how to resolve this very annoying problem? Is there a way to
> backup/copy the Logins and Users?
>
> Maybe I do something wrong when creating my users?
> I create them like this: I go to the Security\Logins\ and assign them
there
quote:
> to the database (on the Database Access-tab). Than I go to the
> Databases\MyDataBase\Users\ and add them there: New -> Database User...
> Anybody got any idea? I woudl really appreciate some help here.
> Thanks a lot,
> Pieter
>
this problem is known as "orphaned users"...
the "imported" database already hase users that are otu of sync with the
corrisponding Login objects, that's to say there's not a corresponding login
for the specified users or the sid do not match in the JOIN sid.sysusers =
sid.syslogins relation, with NULL value result...
you have to fix them using system stored procedure sp_change_users_login
and/or drop the orphan users...
please have a look at
http://www.sqlservercentral.com/col...rokenlogins.asp
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
224071 INF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/?id=224071
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
246133 INF: How To Transfer Logins and Passwords Between SQL Servers
http://support.microsoft.com/?id=246133
168001 PRB: User Logon and/or Permission Errors After Restoring Dump
http://support.microsoft.com/?id=168001
hth
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.5.0 - DbaMgr ver 0.51.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply|||Thanks a lot! I'm gonna take a look at that :-)
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:bu8mr9$equ58$1@.ID-207518.news.uni-berlin.de...
quote:
> hi Pieter,
> "DraguVaso" <pietercoucke@.hotmail.com> ha scritto nel messaggio
> news:4007da7e$0$311$ba620e4c@.news.skynet.be...
> SQL
> the
> and
getting[QUOTE]
password:[QUOTE]
> so
> that
> error
name[QUOTE]
in[QUOTE]
to[QUOTE]
> there
> this problem is known as "orphaned users"...
> the "imported" database already hase users that are otu of sync with the
> corrisponding Login objects, that's to say there's not a corresponding
login
quote:
> for the specified users or the sid do not match in the JOIN sid.sysusers =
> sid.syslogins relation, with NULL value result...
> you have to fix them using system stored procedure sp_change_users_login
> and/or drop the orphan users...
> please have a look at
> http://www.sqlservercentral.com/col...rokenlogins.asp
> 314546 HOW TO: Move Databases Between Computers That Are Running SQL
Server
quote:
> http://support.microsoft.com/?id=314546
> 224071 INF: Moving SQL Server Databases to a New Location with
Detach/Attach
quote:|||I use the following statements to create a script to run on the new server.
> http://support.microsoft.com/?id=224071
> 240872 HOW TO: Resolve Permission Issues When You Move a Database Between
> http://support.microsoft.com/?id=240872
> 246133 INF: How To Transfer Logins and Passwords Between SQL Servers
> http://support.microsoft.com/?id=246133
> 168001 PRB: User Logon and/or Permission Errors After Restoring Dump
> http://support.microsoft.com/?id=168001
> hth
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.5.0 - DbaMgr ver 0.51.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
Note that this is tested for SQL2000 only.
--For SQL logins
SELECT 'sp_addlogin '''+
left(name + ''', ',15), --Adjust for max login length
password,
+ ', ''' +
db_name(dbid) +
+ ''', ''' +
+ language
+ ''', ',
sid,
', skip_encryption'
FROM master.dbo.sysxlogins
where srvid is null
and xstatus&4 <> 4 --isntname
order by name
--For Windows logins
SELECT 'sp_grantlogin '''+ name + ''''
FROM master.dbo.sysxlogins
where srvid is null
and xstatus&4 = 4 --isntname
and name <> 'BUILTIN\ADMINISTRATORS'
order by name
HTH,
Mike Kruchten
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:4007da7e$0$311$ba620e4c@.news.skynet.be...
quote:
> Hi,
> I'm always having problems when I want to move a SQL database to another
SQL
quote:
> Server. The way I do it isl iek this: I make a backup of the database on
the
quote:
> original server, copy it to thje new server, make there a new database,
and
quote:
> restore the backup in that new database.
> So far no problem, but when I want to put put the logins in it I'm getting
> trouble:
> I want to make on the new server a Login with the same name and password:
so
quote:
> I go to the Security\Logins\, and create on. But when I want to assign
that
quote:
> Login to the SPecific Database (on the Database Access-tab), I got an
error
quote:
> which is saying that that there is alreaddy a user or role with that name
> for that database.
> When I go look in the Databases\MyDataBase\Users\ there isn't any user in
> it...
> Anybody knows how to resolve this very annoying problem? Is there a way to
> backup/copy the Logins and Users?
>
> Maybe I do something wrong when creating my users?
> I create them like this: I go to the Security\Logins\ and assign them
there
quote:
> to the database (on the Database Access-tab). Than I go to the
> Databases\MyDataBase\Users\ and add them there: New -> Database User...
> Anybody got any idea? I woudl really appreciate some help here.
> Thanks a lot,
> Pieter
>
>
No comments:
Post a Comment