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 transfer logins between 2k5 instances

Hello,
I am wondering if there is a kb article on how to transfer logins
between two sql2k5 instances? I have seen one for sql2k but not for sql2k5.
Thanks.
JakeHi Jake,
You can use a SSIS package to transfer logins. Also you can check my blog. I
updated the MS procedure to SQL Server 2005. it is in Spanish, but the code
is T-SQL, of course :-)
http://solidqualitylearning.com/blo...02/25/1618.aspx
Regards
Antonio Soto
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Jake" <noreply@.nowhere.com> escribi en el mensaje
news:enqPtusQGHA.5036@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I am wondering if there is a kb article on how to transfer logins
> between two sql2k5 instances? I have seen one for sql2k but not for
> sql2k5. Thanks.
> Jake
>|||Antonio,
Thanks alot for the help.
Jake
"Antonio Soto" <antoniosotorodriguez@.gmail.com> wrote in message
news:OsSMC92QGHA.4608@.tk2msftngp13.phx.gbl...
> Hi Jake,
> You can use a SSIS package to transfer logins. Also you can check my blog.
> I updated the MS procedure to SQL Server 2005. it is in Spanish, but the
> code is T-SQL, of course :-)
> http://solidqualitylearning.com/blo...02/25/1618.aspx
> Regards
> Antonio Soto
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
> "Jake" <noreply@.nowhere.com> escribi en el mensaje
> news:enqPtusQGHA.5036@.TK2MSFTNGP12.phx.gbl...
>|||You can still do it using SSIS (as opposed to DTS) . One thing to watch out
for is that for SQL logins the account is disabled and the password set to
random value as per Books Online "Transfer Logins Task"
Apart from that pretty sure the same rules applies - ie have to fix up with
sp_change_users_login
cheers,
Andy.
"Jake" <noreply@.nowhere.com> wrote in message
news:enqPtusQGHA.5036@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I am wondering if there is a kb article on how to transfer logins
> between two sql2k5 instances? I have seen one for sql2k but not for
> sql2k5. Thanks.
> Jake
>

how to tell if a login has already been created

I want to create a script that will run and create a predetermined set of logins AND dbrolemembership but only if they do not presently exist in the database. I have played around with sp_addlogin and sp_addrolemember. I then checked the sysusers and syslogins table to determine the existence. I cannot get a row to insert into sysusers. Can someone elighten me on where the login & rolemembership is stored. I also spent some time looking for a proc that might give me what i need.

any direction or comments are appreciated.

MikeRole membership is stored in sysusers table of each database (except for tempdb) and login info in master.dbo.syslogins. You right on track based on sp's and tables you mentioned. All you need to do is script those sp's (sp_addlogin and sp_addrolemember) in your QA and you'll have all the answers yourself.|||Thanks for the response.

I have written the scritps to create the users and add them to the correct roles. I would like to test to see if the user and /or role(s) are already existing when I run the script.

PRINT '===> Add logins'
EXEC sp_addlogin 'pubuser','pubuser'
EXEC sp_addlogin 'opsuser','opsuser'

PRINT '===> Add user, attach login and assign permissions for users'
USE Main
EXEC sp_grantdbaccess 'pubuser'
EXEC sp_addrolemember 'db_datareader','pubuser'
EXEC sp_addrolemember 'db_datawriter','pubuser'

EXEC sp_grantdbaccess 'opsuser'
EXEC sp_addrolemember 'db_datareader','opsuser'
EXEC sp_addrolemember 'db_datawriter','opsuser'

The issue / question is when I select * from syslogins I can see the pubuser and opsuser. However, I cannot see a cooresponding entry on sysusers. Still missing a piece somewhere?

mike|||I just ran your script and replaces Main with my database, and when I do a SELECT from sysusers of that database I see both users added. Did I miss anything?

And of course, you'd do something like "if not exist (select1 from master.dbo.syslogins where name = <new_login>) exec sp_addlogin <...>" to validate the presence or abscence of a login, same with users, etc.|||I was using the master sysusers table.

Thanks

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