how to transfer logins,pwds and current permissions

I need to restore a database and would have orphaned users I notice the
Transfer logins KB articles creates those logins and passwords.. But what
about the security such as if the login was dbowner or sysadmin or
processadmin . I am using SQL 2000I mean the transfer of logins and passwords in the MS KB articles
I believe the special sprocs listed there take care of the logins, passwords
and the SIDs so that the users in the databases are not orphaned but I was
just worried about how to transfer the Server roles. I believe the Database
access roles are within the database
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:e86%23PdHaDHA.2032@.TK2MSFTNGP10.phx.gbl...
> I'm not sure which transfer you are referring to (if you mean a mthod
which keep the correct SID or
> not).
> However, assuming that you do have matcing SID's (this is the first thing
to handle):
> Things like db_owner etc are inside the database, hence are transferred
with the restore.
> I'm pretty certain that the transfer of logins doesn't handle stuff in
master for your logins,
> though (sysadmin etc).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eRpy$0EaDHA.4028@.tk2msftngp13.phx.gbl...
> > I need to restore a database and would have orphaned users I notice the
> > Transfer logins KB articles creates those logins and passwords.. But
what
> > about the security such as if the login was dbowner or sysadmin or
> > processadmin . I am using SQL 2000
> >
> >
>

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 users
are not reconnected. (BOL:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/11eefa97-a31f-4359-ba5b-e92328224133.htm
[quote]sp_change_users_login cannot be used with Windows logins.[/quote])
MCDBA 2000
MCSE 2000
"Uri Dimant" wrote:
> Hi
> 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
> >
>
>|||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-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
> >
>
>

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...
>
>