How to sync users and logins in SQL2005 after restore

Does anyone have a script that synchronizes the users and logins in a
database after a restore for SQL 2005?
I used one for SQL 2000, but the domain groups are not synced, and the SQL
users are. So if any one knows how to do this, please post the answer!
The script I used that works half is the following:
[code]
DECLARE @.UserName nvarchar(255)
DECLARE orphanuser_cur cursor for
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and
suser_sname(sid) is null
ORDER BY name
--cannot translate sid to existing user=orphaned
OPEN orphanuser_cur
FETCH NEXT FROM orphanuser_cur INTO @.UserName
WHILE (@.@.fetch_status = 0)
BEGIN
PRINT @.UserName + ' user name being resynced'
EXEC sp_change_users_login 'Update_one', @.UserName, @.UserName
FETCH NEXT FROM orphanuser_cur INTO @.UserName
END
CLOSE orphanuser_cur
DEALLOCATE orphanuser_cur
go
[/code]
MCDBA 2000
MCSE 2000Hi
BOL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/516ef311-e83b-45c9-b9cd-
0e0641774c04.htm
Take a look at this article
http://blogs.msdn.com/lcris/archive/category/11519.aspx
"G? Brander" <GBrander@.discussions.microsoft.com> wrote in message
news:EAE86B34-3DC8-4027-90C7-1029FC2A34AE@.microsoft.com...
> Does anyone have a script that synchronizes the users and logins in a
> database after a restore for SQL 2005?
> I used one for SQL 2000, but the domain groups are not synced, and the SQL
> users are. So if any one knows how to do this, please post the answer!
> The script I used that works half is the following:
> [code]
> DECLARE @.UserName nvarchar(255)
> DECLARE orphanuser_cur cursor for
> SELECT UserName = name
> FROM sysusers
> WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and
> suser_sname(sid) is null
> ORDER BY name
> --cannot translate sid to existing user=orphaned
> OPEN orphanuser_cur
> FETCH NEXT FROM orphanuser_cur INTO @.UserName
> WHILE (@.@.fetch_status = 0)
> BEGIN
> PRINT @.UserName + ' user name being resynced'
> EXEC sp_change_users_login 'Update_one', @.UserName, @.UserName
> FETCH NEXT FROM orphanuser_cur INTO @.UserName
> END
> CLOSE orphanuser_cur
> DEALLOCATE orphanuser_cur
> go
> [/code]
> --
> MCDBA 2000
> MCSE 2000
>|||Thanks for your answer, but the first link does tell someting about changing
the db_owner, and I do not see the connection with my problem.
The second link tells me also nothing about my problem.
My problem is:
After a restore from a database to a server that already has the logins, the
users and logins are not in sync. So the id's from the users in the database
are not corresponding anymore. With the script I supplied in my startpost, I
can reconnect the SQL Server logins, but the Windows groups and Windows user
s
are not reconnected. (BOL:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/11eefa97-a31f-4359-ba5b-e923
28224133.htm
[quote]sp_change_users_login cannot be used with Windows logins.[/qu
ote])
MCDBA 2000
MCSE 2000
"Uri Dimant" wrote:

> Hi
> BOL
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/516ef311-e83b-45c9-b9c
d-0e0641774c04.htm
>
> Take a look at this article
> http://blogs.msdn.com/lcris/archive/category/11519.aspx
>
> "G? Brander" <GBrander@.discussions.microsoft.com> wrote in message
> news:EAE86B34-3DC8-4027-90C7-1029FC2A34AE@.microsoft.com...
>
>|||The database was the Microsoft CRM 3.0 database, and I solved it now, by
scripting the schema's and users, changed the scripts so, that the users
where connected tot the appropiate login's, deleted the schema's and users
and finaly recreated the users and schema's.
There should however be an easier way, because when there are objects owned
by schema's you are not able to delete the schema's.
So if anyone knows the answer, please respond.
--
MCDBA 2000
MCSE 2000
"Uri Dimant" wrote:

> Hi
> BOL
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/516ef311-e83b-45c9-b9c
d-0e0641774c04.htm
>
> Take a look at this article
> http://blogs.msdn.com/lcris/archive/category/11519.aspx
>
> "G? Brander" <GBrander@.discussions.microsoft.com> wrote in message
> news:EAE86B34-3DC8-4027-90C7-1029FC2A34AE@.microsoft.com...
>
>