sql login permissions: transfer

Showing posts with label transfer. Show all posts

Monday, March 12, 2012

Logins transfer problem

I have almost 200 SQL Server accounts on a SQL Server 7.0 server(SP2). I need
to transfer these accounts on to an SQL Server 2005 server(SP2). I used
http://support.microsoft.com/kb/246133
but it does not transfer the paswords. I know this because when I try to
connect with an account using the password on the old server, it does not
connect.
The only difference between the 2 servers, the old one has the collation of
SQL_Latin1_General_Cp437_BIN
and the new one has
SQL_Latin1_General_Cp1_CI_AS
Any ideas how I can transfer the passwords correctly ?
T.I.ADid you manually run SP_Help_RevLogin stored procedure? If not then you will
need to run the procdedure manually and take the output of that procedure and
execute it against the new SQL Server.
It has always worked for me.
--
Thank you,
Saleem Hakani
HTTP://WWW.SQLCOMMUNITY.COM (World Wide SQL Server Community)
SQLTips, Scripts, Discussions, Blogs, Articles, Radio and a lot of SQL
Server Fun.
"DXC" wrote:
> I have almost 200 SQL Server accounts on a SQL Server 7.0 server(SP2). I need
> to transfer these accounts on to an SQL Server 2005 server(SP2). I used
> http://support.microsoft.com/kb/246133
> but it does not transfer the paswords. I know this because when I try to
> connect with an account using the password on the old server, it does not
> connect.
> The only difference between the 2 servers, the old one has the collation of
> SQL_Latin1_General_Cp437_BIN
> and the new one has
> SQL_Latin1_General_Cp1_CI_AS
> Any ideas how I can transfer the passwords correctly ?
> T.I.A|||You don't have to manually execute it. The whole script does that for you. If
you read the document, you just have to take the output and run it on the new
server.
"Saleem Hakani" wrote:
> Did you manually run SP_Help_RevLogin stored procedure? If not then you will
> need to run the procdedure manually and take the output of that procedure and
> execute it against the new SQL Server.
> It has always worked for me.
> --
> Thank you,
> Saleem Hakani
> HTTP://WWW.SQLCOMMUNITY.COM (World Wide SQL Server Community)
> SQLTips, Scripts, Discussions, Blogs, Articles, Radio and a lot of SQL
> Server Fun.
>
> "DXC" wrote:
> > I have almost 200 SQL Server accounts on a SQL Server 7.0 server(SP2). I need
> > to transfer these accounts on to an SQL Server 2005 server(SP2). I used
> >
> > http://support.microsoft.com/kb/246133
> >
> > but it does not transfer the paswords. I know this because when I try to
> > connect with an account using the password on the old server, it does not
> > connect.
> >
> > The only difference between the 2 servers, the old one has the collation of
> >
> > SQL_Latin1_General_Cp437_BIN
> >
> > and the new one has
> >
> > SQL_Latin1_General_Cp1_CI_AS
> >
> > Any ideas how I can transfer the passwords correctly ?
> >
> > T.I.A

logins transfer

How do I transfer logins from one SQL server to another? Thanks.In SQL Server 2000 use the Transfer Logins Task. On the Transfer Logins
Properties just specify the source and destination servers and the logins to
transfer.
Ben Nevarez, MCDBA, OCP
Database Administrator
"00KobeBrian" wrote:
> How do I transfer logins from one SQL server to another? Thanks.
>
>|||Oops, I forgot to mention that the Transfer Logins Task is available on the
DTS packages so you will need to create a DTS package first.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Ben Nevarez" wrote:
> In SQL Server 2000 use the Transfer Logins Task. On the Transfer Logins
> Properties just specify the source and destination servers and the logins to
> transfer.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "00KobeBrian" wrote:
> > How do I transfer logins from one SQL server to another? Thanks.
> >
> >
> >|||Look at
http://support.microsoft.com/default.aspx?kbid=246133
Regards
Amish Shah|||Hi All,
Great information sharing.
KobeBrian, let me know if you still have concerns in this issue. Feel free
to post back.
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>From: "amish" <shahamishm@.gmail.com>
>>Newsgroups: microsoft.public.sqlserver.server
>>Subject: Re: logins transfer
>>Date: 9 May 2006 22:31:23 -0700
>>Organization: http://groups.google.com
>>Lines: 6
>>Message-ID: <1147239083.882328.121810@.u72g2000cwu.googlegroups.com>
>>References: <u$D5aT#cGHA.3348@.TK2MSFTNGP03.phx.gbl>
>> <5AB1EFD4-413C-49BE-A101-16470D66CD73@.microsoft.com>
>> <1B440201-B2C5-4D07-B4AC-8DE3085AAC6D@.microsoft.com>
>>NNTP-Posting-Host: 203.27.235.5
>>Mime-Version: 1.0
>>Content-Type: text/plain; charset="iso-8859-1"
>>X-Trace: posting.google.com 1147239089 1034 127.0.0.1 (10 May 2006
05:31:29 GMT)
>>X-Complaints-To: groups-abuse@.google.com
>>NNTP-Posting-Date: Wed, 10 May 2006 05:31:29 +0000 (UTC)
>>In-Reply-To: <1B440201-B2C5-4D07-B4AC-8DE3085AAC6D@.microsoft.com>
>>User-Agent: G2/0.2
>>X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET
CLR 2.0.50727),gzip(gfe),gzip(gfe)
>>X-HTTP-Via: 1.0 MLXLDCR01
>>Complaints-To: groups-abuse@.google.com
>>Injection-Info: u72g2000cwu.googlegroups.com; posting-host=203.27.235.5;
>> posting-account=EMkDJA0AAABVpxHyye2-j9m4ZUnK4SBu
>>Path:
TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTFEEDS01.phx.gbl!newsfeed.c
w.net!cw.net!news-FFM2.ecrc.de!news.glorb.com!postnews.google.com!u72g2000cw
u.googlegroups.com!not-for-mail
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:430903
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>Look at
>>http://support.microsoft.com/default.aspx?kbid=246133
>>Regards
>>Amish Shah
>>

Logins from 2000 to 2005

As from what i see there are different system files for logins.

Is there a way to transfer SQL 2000 logins to SQL 2005 i am used to the

sp_help_revlogin i think thats the script..in 2000

Is there a way to convert all logins from SQL 2000 to SQL 2005

Did you use the updated version of the procedure ?

http://blogs.msdn.com/lcris/archive/2006/04/03/567680.aspx

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

No not seen that one...so just run on SQL 2000 and then run on SQL 2005.

Have you used it.

Thanks

|||You should use that one on your machine if you are using a SQL2k5.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Friday, March 9, 2012

logins

Hi,
How do I transfer all logins from sql2k server to a new sql2005 box?
ThanksYou can use many approaches:
- Copy Database Wizard
- A SQL 2000 DTS package
- A SQL 2005 SSIS package
Also you can use the following script
http://solidqualitylearning.com/blo...02/25/1618.aspx
Regards
Antonio Soto
Solid Quality Learning
http://www.sqlu.com
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.
"mecn" <mecn2002@.yahoo.com> escribi en el mensaje
news:umAey80UGHA.1688@.TK2MSFTNGP11.phx.gbl...
> Hi,
> How do I transfer all logins from sql2k server to a new sql2005 box?
> Thanks
>|||Thanks, All I need to do is transfer to master.logins table
"Antonio Soto" <antoniosotorodriguez@.gmail.com> wrote in message
news:uwyt2H3UGHA.4300@.TK2MSFTNGP14.phx.gbl...
> You can use many approaches:
> - Copy Database Wizard
> - A SQL 2000 DTS package
> - A SQL 2005 SSIS package
> Also you can use the following script
> http://solidqualitylearning.com/blo...02/25/1618.aspx
> Regards
>
> --
> Antonio Soto
> Solid Quality Learning
> http://www.sqlu.com
> 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.
> "mecn" <mecn2002@.yahoo.com> escribi en el mensaje
> news:umAey80UGHA.1688@.TK2MSFTNGP11.phx.gbl...
>|||thanks
"Antonio Soto" <antoniosotorodriguez@.gmail.com> wrote in message
news:uwyt2H3UGHA.4300@.TK2MSFTNGP14.phx.gbl...
> You can use many approaches:
> - Copy Database Wizard
> - A SQL 2000 DTS package
> - A SQL 2005 SSIS package
> Also you can use the following script
> http://solidqualitylearning.com/blo...02/25/1618.aspx
> Regards
>
> --
> Antonio Soto
> Solid Quality Learning
> http://www.sqlu.com
> 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.
> "mecn" <mecn2002@.yahoo.com> escribi en el mensaje
> news:umAey80UGHA.1688@.TK2MSFTNGP11.phx.gbl...
>

Wednesday, March 7, 2012

Login Transfers

I have used this script generator quite successfully to generate transfer
login scripts with encrypted passwords. However, on one server its not
working. I thought it could be collation issue on the machine where the
script is generated. So, I remotely generated on the source server and
applied it on the destination server (both case insensitive sort order on SQL
2K). However, its not working.
Anyone any idea?
SCRIPT
=====
select 'EXEC sp_addlogin '''+name+''', ', CONVERT(VARBINARY(32), password),
', @.encryptopt = ''skip_encryption'''
from syslogins
where name not in ('sa', 'BUILTIN\Administrators', 'repl_publisher',
'repl_subscriber')
order by name
SELECT 'EXEC sp_change_users_login ''Report'''
SELECT 'EXEC sp_change_users_login ''Update_One'', '''+name+''', '''+name+''''
from syslogins
where name not in ('sa', 'BUILTIN\Administrators', 'repl_publisher',
'repl_subscriber')
order by name
--
Regards,
MZeeshanHello MZeeshan,
What is the error message you encountered?
Also, I think the following article shall be helpful
246133 HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/?id=246133
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Login Transfers
| thread-index: AcVVrZN1DsG5iPFdTbmG7jAmcjowmQ==| X-WBNR-Posting-Host: 208.250.29.8
| From: "=?Utf-8?B?TVplZXNoYW4=?=" <mzeeshan@.community.nospam>
| Subject: Login Transfers
| Date: Tue, 10 May 2005 15:14:04 -0700
| Lines: 31
| Message-ID: <B9EA36FE-4151-41E7-A409-A7755F1F26F3@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:55748
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| I have used this script generator quite successfully to generate transfer
| login scripts with encrypted passwords. However, on one server its not
| working. I thought it could be collation issue on the machine where the
| script is generated. So, I remotely generated on the source server and
| applied it on the destination server (both case insensitive sort order on
SQL
| 2K). However, its not working.
|
| Anyone any idea?
|
| SCRIPT
| =====|
| select 'EXEC sp_addlogin '''+name+''', ', CONVERT(VARBINARY(32),
password),
| ', @.encryptopt = ''skip_encryption'''
| from syslogins
| where name not in ('sa', 'BUILTIN\Administrators', 'repl_publisher',
| 'repl_subscriber')
| order by name
|
| SELECT 'EXEC sp_change_users_login ''Report'''
|
| SELECT 'EXEC sp_change_users_login ''Update_One'', '''+name+''',
'''+name+''''
| from syslogins
| where name not in ('sa', 'BUILTIN\Administrators', 'repl_publisher',
| 'repl_subscriber')
| order by name
|
|
| --
| Regards,
| MZeeshan
||||When I tried logging in the password from source server didn't work on
destination.
--
Regards,
MZeeshan
"Peter Yang [MSFT]" wrote:
> Hello MZeeshan,
> What is the error message you encountered?
> Also, I think the following article shall be helpful
> 246133 HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://support.microsoft.com/?id=246133
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
> --
> | Thread-Topic: Login Transfers
> | thread-index: AcVVrZN1DsG5iPFdTbmG7jAmcjowmQ==> | X-WBNR-Posting-Host: 208.250.29.8
> | From: "=?Utf-8?B?TVplZXNoYW4=?=" <mzeeshan@.community.nospam>
> | Subject: Login Transfers
> | Date: Tue, 10 May 2005 15:14:04 -0700
> | Lines: 31
> | Message-ID: <B9EA36FE-4151-41E7-A409-A7755F1F26F3@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:55748
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | I have used this script generator quite successfully to generate transfer
> | login scripts with encrypted passwords. However, on one server its not
> | working. I thought it could be collation issue on the machine where the
> | script is generated. So, I remotely generated on the source server and
> | applied it on the destination server (both case insensitive sort order on
> SQL
> | 2K). However, its not working.
> |
> | Anyone any idea?
> |
> | SCRIPT
> | =====> |
> | select 'EXEC sp_addlogin '''+name+''', ', CONVERT(VARBINARY(32),
> password),
> | ', @.encryptopt = ''skip_encryption'''
> | from syslogins
> | where name not in ('sa', 'BUILTIN\Administrators', 'repl_publisher',
> | 'repl_subscriber')
> | order by name
> |
> | SELECT 'EXEC sp_change_users_login ''Report'''
> |
> | SELECT 'EXEC sp_change_users_login ''Update_One'', '''+name+''',
> '''+name+''''
> | from syslogins
> | where name not in ('sa', 'BUILTIN\Administrators', 'repl_publisher',
> | 'repl_subscriber')
> | order by name
> |
> |
> | --
> | Regards,
> | MZeeshan
> |
>|||Hello MZeeshan,
I think the issue can occur if the logins you want to transfe has already
existed in the destination database.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Login Transfers
| thread-index: AcVWHVT9ma0LUaspRJuQWeBKhalJyw==| X-WBNR-Posting-Host: 67.167.85.152
| From: "=?Utf-8?B?TVplZXNoYW4=?=" <mzeeshan@.community.nospam>
| References: <B9EA36FE-4151-41E7-A409-A7755F1F26F3@.microsoft.com>
<DIIRP2eVFHA.3336@.TK2MSFTNGXA01.phx.gbl>
| Subject: RE: Login Transfers
| Date: Wed, 11 May 2005 04:34:03 -0700
| Lines: 97
| Message-ID: <CEDCC44D-ED7A-44D0-A63C-6F4311CCE966@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:55812
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| When I tried logging in the password from source server didn't work on
| destination.
|
| --
| Regards,
| MZeeshan
|
|
| "Peter Yang [MSFT]" wrote:
|
| > Hello MZeeshan,
| >
| > What is the error message you encountered?
| >
| > Also, I think the following article shall be helpful
| >
| > 246133 HOW TO: Transfer Logins and Passwords Between Instances of SQL
Server
| > http://support.microsoft.com/?id=246133
| >
| > Regards,
| >
| > Peter Yang
| > MCSE2000/2003, MCSA, MCDBA
| > Microsoft Online Partner Support
| >
| > When responding to posts, please "Reply to Group" via your newsreader
so
| > that others may learn and benefit from your issue.
| >
| > =====================================================| >
| >
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| >
| >
| >
| > --
| > | Thread-Topic: Login Transfers
| > | thread-index: AcVVrZN1DsG5iPFdTbmG7jAmcjowmQ==| > | X-WBNR-Posting-Host: 208.250.29.8
| > | From: "=?Utf-8?B?TVplZXNoYW4=?=" <mzeeshan@.community.nospam>
| > | Subject: Login Transfers
| > | Date: Tue, 10 May 2005 15:14:04 -0700
| > | Lines: 31
| > | Message-ID: <B9EA36FE-4151-41E7-A409-A7755F1F26F3@.microsoft.com>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 7bit
| > | X-Newsreader: Microsoft CDO for Windows 2000
| > | Content-Class: urn:content-classes:message
| > | Importance: normal
| > | Priority: normal
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > | Newsgroups: microsoft.public.sqlserver.server
| > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| > | Path:
TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
| > | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:55748
| > | X-Tomcat-NG: microsoft.public.sqlserver.server
| > |
| > | I have used this script generator quite successfully to generate
transfer
| > | login scripts with encrypted passwords. However, on one server its
not
| > | working. I thought it could be collation issue on the machine where
the
| > | script is generated. So, I remotely generated on the source server
and
| > | applied it on the destination server (both case insensitive sort
order on
| > SQL
| > | 2K). However, its not working.
| > |
| > | Anyone any idea?
| > |
| > | SCRIPT
| > | =====| > |
| > | select 'EXEC sp_addlogin '''+name+''', ', CONVERT(VARBINARY(32),
| > password),
| > | ', @.encryptopt = ''skip_encryption'''
| > | from syslogins
| > | where name not in ('sa', 'BUILTIN\Administrators', 'repl_publisher',
| > | 'repl_subscriber')
| > | order by name
| > |
| > | SELECT 'EXEC sp_change_users_login ''Report'''
| > |
| > | SELECT 'EXEC sp_change_users_login ''Update_One'', '''+name+''',
| > '''+name+''''
| > | from syslogins
| > | where name not in ('sa', 'BUILTIN\Administrators', 'repl_publisher',
| > | 'repl_subscriber')
| > | order by name
| > |
| > |
| > | --
| > | Regards,
| > | MZeeshan
| > |
| >
| >
||||Peter,
I did delete the logins before transferring.
But, I would like to thank you as the link you provided earlier to create
those stored procs in 'master' database did indeed helped me in transferring
the logins to destination server.
Thank you!
--
Regards,
MZeeshan
"Peter Yang [MSFT]" wrote:
> Hello MZeeshan,
> I think the issue can occur if the logins you want to transfe has already
> existed in the destination database.
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
> --
> | Thread-Topic: Login Transfers
> | thread-index: AcVWHVT9ma0LUaspRJuQWeBKhalJyw==> | X-WBNR-Posting-Host: 67.167.85.152
> | From: "=?Utf-8?B?TVplZXNoYW4=?=" <mzeeshan@.community.nospam>
> | References: <B9EA36FE-4151-41E7-A409-A7755F1F26F3@.microsoft.com>
> <DIIRP2eVFHA.3336@.TK2MSFTNGXA01.phx.gbl>
> | Subject: RE: Login Transfers
> | Date: Wed, 11 May 2005 04:34:03 -0700
> | Lines: 97
> | Message-ID: <CEDCC44D-ED7A-44D0-A63C-6F4311CCE966@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:55812
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | When I tried logging in the password from source server didn't work on
> | destination.
> |
> | --
> | Regards,
> | MZeeshan
> |
> |
> | "Peter Yang [MSFT]" wrote:
> |
> | > Hello MZeeshan,
> | >
> | > What is the error message you encountered?
> | >
> | > Also, I think the following article shall be helpful
> | >
> | > 246133 HOW TO: Transfer Logins and Passwords Between Instances of SQL
> Server
> | > http://support.microsoft.com/?id=246133
> | >
> | > Regards,
> | >
> | > Peter Yang
> | > MCSE2000/2003, MCSA, MCDBA
> | > Microsoft Online Partner Support
> | >
> | > When responding to posts, please "Reply to Group" via your newsreader
> so
> | > that others may learn and benefit from your issue.
> | >
> | > =====================================================> | >
> | >
> | > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> | >
> | >
> | >
> | >
> | > --
> | > | Thread-Topic: Login Transfers
> | > | thread-index: AcVVrZN1DsG5iPFdTbmG7jAmcjowmQ==> | > | X-WBNR-Posting-Host: 208.250.29.8
> | > | From: "=?Utf-8?B?TVplZXNoYW4=?=" <mzeeshan@.community.nospam>
> | > | Subject: Login Transfers
> | > | Date: Tue, 10 May 2005 15:14:04 -0700
> | > | Lines: 31
> | > | Message-ID: <B9EA36FE-4151-41E7-A409-A7755F1F26F3@.microsoft.com>
> | > | MIME-Version: 1.0
> | > | Content-Type: text/plain;
> | > | charset="Utf-8"
> | > | Content-Transfer-Encoding: 7bit
> | > | X-Newsreader: Microsoft CDO for Windows 2000
> | > | Content-Class: urn:content-classes:message
> | > | Importance: normal
> | > | Priority: normal
> | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | > | Newsgroups: microsoft.public.sqlserver.server
> | > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | > | Path:
> TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | > | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:55748
> | > | X-Tomcat-NG: microsoft.public.sqlserver.server
> | > |
> | > | I have used this script generator quite successfully to generate
> transfer
> | > | login scripts with encrypted passwords. However, on one server its
> not
> | > | working. I thought it could be collation issue on the machine where
> the
> | > | script is generated. So, I remotely generated on the source server
> and
> | > | applied it on the destination server (both case insensitive sort
> order on
> | > SQL
> | > | 2K). However, its not working.
> | > |
> | > | Anyone any idea?
> | > |
> | > | SCRIPT
> | > | =====> | > |
> | > | select 'EXEC sp_addlogin '''+name+''', ', CONVERT(VARBINARY(32),
> | > password),
> | > | ', @.encryptopt = ''skip_encryption'''
> | > | from syslogins
> | > | where name not in ('sa', 'BUILTIN\Administrators', 'repl_publisher',
> | > | 'repl_subscriber')
> | > | order by name
> | > |
> | > | SELECT 'EXEC sp_change_users_login ''Report'''
> | > |
> | > | SELECT 'EXEC sp_change_users_login ''Update_One'', '''+name+''',
> | > '''+name+''''
> | > | from syslogins
> | > | where name not in ('sa', 'BUILTIN\Administrators', 'repl_publisher',
> | > | 'repl_subscriber')
> | > | order by name
> | > |
> | > |
> | > | --
> | > | Regards,
> | > | MZeeshan
> | > |
> | >
> | >
> |
>|||Hello MZeeshan,
Welcome! :-)
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Login Transfers
| thread-index: AcVW+D9xud2lQItISX2USN/hHCRtBQ==| X-WBNR-Posting-Host: 208.250.29.8
| From: "=?Utf-8?B?TVplZXNoYW4=?=" <mzeeshan@.community.nospam>
| References: <B9EA36FE-4151-41E7-A409-A7755F1F26F3@.microsoft.com>
<DIIRP2eVFHA.3336@.TK2MSFTNGXA01.phx.gbl>
<CEDCC44D-ED7A-44D0-A63C-6F4311CCE966@.microsoft.com>
<Lpd1LIrVFHA.3336@.TK2MSFTNGXA01.phx.gbl>
| Subject: RE: Login Transfers
| Date: Thu, 12 May 2005 06:41:06 -0700
| Lines: 174
| Message-ID: <68F79652-11A0-4ABB-9015-1BB3D5924B85@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:55945
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Peter,
|
| I did delete the logins before transferring.
|
| But, I would like to thank you as the link you provided earlier to create
| those stored procs in 'master' database did indeed helped me in
transferring
| the logins to destination server.
|
| Thank you!
|
| --
| Regards,
| MZeeshan
|
|
| "Peter Yang [MSFT]" wrote:
|
| > Hello MZeeshan,
| >
| > I think the issue can occur if the logins you want to transfe has
already
| > existed in the destination database.
| >
| > Regards,
| >
| > Peter Yang
| > MCSE2000/2003, MCSA, MCDBA
| > Microsoft Online Partner Support
| >
| > When responding to posts, please "Reply to Group" via your newsreader
so
| > that others may learn and benefit from your issue.
| >
| > =====================================================| >
| >
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| >
| >
| >
| > --
| > | Thread-Topic: Login Transfers
| > | thread-index: AcVWHVT9ma0LUaspRJuQWeBKhalJyw==| > | X-WBNR-Posting-Host: 67.167.85.152
| > | From: "=?Utf-8?B?TVplZXNoYW4=?=" <mzeeshan@.community.nospam>
| > | References: <B9EA36FE-4151-41E7-A409-A7755F1F26F3@.microsoft.com>
| > <DIIRP2eVFHA.3336@.TK2MSFTNGXA01.phx.gbl>
| > | Subject: RE: Login Transfers
| > | Date: Wed, 11 May 2005 04:34:03 -0700
| > | Lines: 97
| > | Message-ID: <CEDCC44D-ED7A-44D0-A63C-6F4311CCE966@.microsoft.com>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 7bit
| > | X-Newsreader: Microsoft CDO for Windows 2000
| > | Content-Class: urn:content-classes:message
| > | Importance: normal
| > | Priority: normal
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > | Newsgroups: microsoft.public.sqlserver.server
| > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| > | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| > | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:55812
| > | X-Tomcat-NG: microsoft.public.sqlserver.server
| > |
| > | When I tried logging in the password from source server didn't work
on
| > | destination.
| > |
| > | --
| > | Regards,
| > | MZeeshan
| > |
| > |
| > | "Peter Yang [MSFT]" wrote:
| > |
| > | > Hello MZeeshan,
| > | >
| > | > What is the error message you encountered?
| > | >
| > | > Also, I think the following article shall be helpful
| > | >
| > | > 246133 HOW TO: Transfer Logins and Passwords Between Instances of
SQL
| > Server
| > | > http://support.microsoft.com/?id=246133
| > | >
| > | > Regards,
| > | >
| > | > Peter Yang
| > | > MCSE2000/2003, MCSA, MCDBA
| > | > Microsoft Online Partner Support
| > | >
| > | > When responding to posts, please "Reply to Group" via your
newsreader
| > so
| > | > that others may learn and benefit from your issue.
| > | >
| > | > =====================================================| > | >
| > | >
| > | > This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| > | >
| > | >
| > | >
| > | >
| > | > --
| > | > | Thread-Topic: Login Transfers
| > | > | thread-index: AcVVrZN1DsG5iPFdTbmG7jAmcjowmQ==| > | > | X-WBNR-Posting-Host: 208.250.29.8
| > | > | From: "=?Utf-8?B?TVplZXNoYW4=?=" <mzeeshan@.community.nospam>
| > | > | Subject: Login Transfers
| > | > | Date: Tue, 10 May 2005 15:14:04 -0700
| > | > | Lines: 31
| > | > | Message-ID: <B9EA36FE-4151-41E7-A409-A7755F1F26F3@.microsoft.com>
| > | > | MIME-Version: 1.0
| > | > | Content-Type: text/plain;
| > | > | charset="Utf-8"
| > | > | Content-Transfer-Encoding: 7bit
| > | > | X-Newsreader: Microsoft CDO for Windows 2000
| > | > | Content-Class: urn:content-classes:message
| > | > | Importance: normal
| > | > | Priority: normal
| > | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > | > | Newsgroups: microsoft.public.sqlserver.server
| > | > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| > | > | Path:
| > TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
| > | > | Xref: TK2MSFTNGXA01.phx.gbl
microsoft.public.sqlserver.server:55748
| > | > | X-Tomcat-NG: microsoft.public.sqlserver.server
| > | > |
| > | > | I have used this script generator quite successfully to generate
| > transfer
| > | > | login scripts with encrypted passwords. However, on one server
its
| > not
| > | > | working. I thought it could be collation issue on the machine
where
| > the
| > | > | script is generated. So, I remotely generated on the source
server
| > and
| > | > | applied it on the destination server (both case insensitive sort
| > order on
| > | > SQL
| > | > | 2K). However, its not working.
| > | > |
| > | > | Anyone any idea?
| > | > |
| > | > | SCRIPT
| > | > | =====| > | > |
| > | > | select 'EXEC sp_addlogin '''+name+''', ', CONVERT(VARBINARY(32),
| > | > password),
| > | > | ', @.encryptopt = ''skip_encryption'''
| > | > | from syslogins
| > | > | where name not in ('sa', 'BUILTIN\Administrators',
'repl_publisher',
| > | > | 'repl_subscriber')
| > | > | order by name
| > | > |
| > | > | SELECT 'EXEC sp_change_users_login ''Report'''
| > | > |
| > | > | SELECT 'EXEC sp_change_users_login ''Update_One'', '''+name+''',
| > | > '''+name+''''
| > | > | from syslogins
| > | > | where name not in ('sa', 'BUILTIN\Administrators',
'repl_publisher',
| > | > | 'repl_subscriber')
| > | > | order by name
| > | > |
| > | > |
| > | > | --
| > | > | Regards,
| > | > | MZeeshan
| > | > |
| > | >
| > | >
| > |
| >
| >
|

Monday, February 20, 2012

login password changed

We have a testing server. Sometimes we need to move some logins and users
from production server to the testing server, I use transfer login or copy
objects dts,
Then some of our web pages cannot be acceessed by the logins.
And I checked there was no orphaned users.
Finally found the password is changed while transfer, change it to oringinal
password, then it works.
My question is why the password changed during transfer or moving logins or
users?
We didn't change it manually at all.
Is this a bug ? or is this a problem many users have?
ThanksI think this is done for security reasons. The passwords after transfer
gets encrypted that way SA or equivalent user will reset the passwords.
Thereby implicit privileges will not be available to the login without the
administrator's knowledge.
Bhanu.
"Ann" <Ann@.discussions.microsoft.com> wrote in message
news:4446F314-A347-4EBC-B621-798CFE959B12@.microsoft.com...
> We have a testing server. Sometimes we need to move some logins and users
> from production server to the testing server, I use transfer login or copy
> objects dts,
> Then some of our web pages cannot be acceessed by the logins.
> And I checked there was no orphaned users.
> Finally found the password is changed while transfer, change it to
oringinal
> password, then it works.
> My question is why the password changed during transfer or moving logins
or
> users?
> We didn't change it manually at all.
> Is this a bug ? or is this a problem many users have?
> Thanks|||You may avoid this problem by using the master..sp_help_revlogin stored proc
available from Microsoft (reference Knowledge Base Article 246133). This
stored procedure will generate a script on the source server that can be
executed on the target server to create the logins with their original SIDs
and passwords.
"Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
news:ufoToIhsEHA.3320@.TK2MSFTNGP15.phx.gbl...
> I think this is done for security reasons. The passwords after transfer
> gets encrypted that way SA or equivalent user will reset the passwords.
> Thereby implicit privileges will not be available to the login without the
> administrator's knowledge.
> Bhanu.
>
> "Ann" <Ann@.discussions.microsoft.com> wrote in message
> news:4446F314-A347-4EBC-B621-798CFE959B12@.microsoft.com...
> > We have a testing server. Sometimes we need to move some logins and
users
> > from production server to the testing server, I use transfer login or
copy
> > objects dts,
> > Then some of our web pages cannot be acceessed by the logins.
> >
> > And I checked there was no orphaned users.
> >
> > Finally found the password is changed while transfer, change it to
> oringinal
> > password, then it works.
> >
> > My question is why the password changed during transfer or moving logins
> or
> > users?
> > We didn't change it manually at all.
> >
> > Is this a bug ? or is this a problem many users have?
> >
> > Thanks
>|||First, actually we have a udl file that we know what the passwords are.
The file works good on the production server, but not the testing server,
where we moved to. After I changed the password according to it on the
testing server, it works. So there is no issue about encrypt and reset. I do
user sa or sys admin account to do all the transfers.
Second I know this stored procedure, it says it works in only domain
environment, while ours is in workgroup enviroment, so guess not work for us.
The dba here said we don't want to put sql servers in public domain for
security reasons.
So really want to know if this is a bug or other users may have this problem
too?
I mean after transfering log in, password changed'
Thanks
"TomB" wrote:
> You may avoid this problem by using the master..sp_help_revlogin stored proc
> available from Microsoft (reference Knowledge Base Article 246133). This
> stored procedure will generate a script on the source server that can be
> executed on the target server to create the logins with their original SIDs
> and passwords.
> "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
> news:ufoToIhsEHA.3320@.TK2MSFTNGP15.phx.gbl...
> > I think this is done for security reasons. The passwords after transfer
> > gets encrypted that way SA or equivalent user will reset the passwords.
> > Thereby implicit privileges will not be available to the login without the
> > administrator's knowledge.
> >
> > Bhanu.
> >
> >
> > "Ann" <Ann@.discussions.microsoft.com> wrote in message
> > news:4446F314-A347-4EBC-B621-798CFE959B12@.microsoft.com...
> > > We have a testing server. Sometimes we need to move some logins and
> users
> > > from production server to the testing server, I use transfer login or
> copy
> > > objects dts,
> > > Then some of our web pages cannot be acceessed by the logins.
> > >
> > > And I checked there was no orphaned users.
> > >
> > > Finally found the password is changed while transfer, change it to
> > oringinal
> > > password, then it works.
> > >
> > > My question is why the password changed during transfer or moving logins
> > or
> > > users?
> > > We didn't change it manually at all.
> > >
> > > Is this a bug ? or is this a problem many users have?
> > >
> > > Thanks
> >
> >
>
>|||> Second I know this stored procedure, it says it works in only domain
> environment,
I didn't find such a statement in the KB. Can you elaborate?. And, domain structure is totally
irrelevant for SQL server logins.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ann" <Ann@.discussions.microsoft.com> wrote in message
news:19E05F0B-57C7-41A3-8087-CF0749C6DE15@.microsoft.com...
> First, actually we have a udl file that we know what the passwords are.
> The file works good on the production server, but not the testing server,
> where we moved to. After I changed the password according to it on the
> testing server, it works. So there is no issue about encrypt and reset. I do
> user sa or sys admin account to do all the transfers.
> Second I know this stored procedure, it says it works in only domain
> environment, while ours is in workgroup enviroment, so guess not work for us.
> The dba here said we don't want to put sql servers in public domain for
> security reasons.
> So really want to know if this is a bug or other users may have this problem
> too?
> I mean after transfering log in, password changed'
> Thanks
>
>
>
> "TomB" wrote:
>> You may avoid this problem by using the master..sp_help_revlogin stored proc
>> available from Microsoft (reference Knowledge Base Article 246133). This
>> stored procedure will generate a script on the source server that can be
>> executed on the target server to create the logins with their original SIDs
>> and passwords.
>> "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
>> news:ufoToIhsEHA.3320@.TK2MSFTNGP15.phx.gbl...
>> > I think this is done for security reasons. The passwords after transfer
>> > gets encrypted that way SA or equivalent user will reset the passwords.
>> > Thereby implicit privileges will not be available to the login without the
>> > administrator's knowledge.
>> >
>> > Bhanu.
>> >
>> >
>> > "Ann" <Ann@.discussions.microsoft.com> wrote in message
>> > news:4446F314-A347-4EBC-B621-798CFE959B12@.microsoft.com...
>> > > We have a testing server. Sometimes we need to move some logins and
>> users
>> > > from production server to the testing server, I use transfer login or
>> copy
>> > > objects dts,
>> > > Then some of our web pages cannot be acceessed by the logins.
>> > >
>> > > And I checked there was no orphaned users.
>> > >
>> > > Finally found the password is changed while transfer, change it to
>> > oringinal
>> > > password, then it works.
>> > >
>> > > My question is why the password changed during transfer or moving logins
>> > or
>> > > users?
>> > > We didn't change it manually at all.
>> > >
>> > > Is this a bug ? or is this a problem many users have?
>> > >
>> > > Thanks
>> >
>> >
>>|||Here is what I copied from the article, two paragraphs:
Important The SQL Server 2000 destination server cannot be running the
64-bit version of SQL Server 2000. DTS components for the 64-bit version of
SQL Server 2000 are not available. If you are importing logins from an
instance of SQL Server that is on a separate computer, your instance of SQL
Server will must be running under a Domain Account to complete the task.
Remarks
â?¢ Review the output script carefully before you run it on the destination
SQL Server. If you have to transfer logins to an instance of SQL Server in a
different domain than the source instance of SQL Server, edit the script
generated by the sp_help_revlogin procedure, and replace the domain name with
the new domain in the sp_grantlogin statements. Because the integrated logins
granted access in the new domain will not have the same SID as the logins in
the original domain, the database users will be orphaned from these logins.
To resolve these orphaned users, see the articles referenced in the following
bullet item. If you transfer integrated logins between instances of SQL
Servers in the same domain, the same SID is used and the user is not likely
to be orphaned.
"Tibor Karaszi" wrote:
> > Second I know this stored procedure, it says it works in only domain
> > environment,
> I didn't find such a statement in the KB. Can you elaborate?. And, domain structure is totally
> irrelevant for SQL server logins.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ann" <Ann@.discussions.microsoft.com> wrote in message
> news:19E05F0B-57C7-41A3-8087-CF0749C6DE15@.microsoft.com...
> > First, actually we have a udl file that we know what the passwords are.
> > The file works good on the production server, but not the testing server,
> > where we moved to. After I changed the password according to it on the
> > testing server, it works. So there is no issue about encrypt and reset. I do
> > user sa or sys admin account to do all the transfers.
> >
> > Second I know this stored procedure, it says it works in only domain
> > environment, while ours is in workgroup enviroment, so guess not work for us.
> > The dba here said we don't want to put sql servers in public domain for
> > security reasons.
> >
> > So really want to know if this is a bug or other users may have this problem
> > too?
> > I mean after transfering log in, password changed'
> > Thanks
> >
> >
> >
> >
> >
> >
> > "TomB" wrote:
> >
> >> You may avoid this problem by using the master..sp_help_revlogin stored proc
> >> available from Microsoft (reference Knowledge Base Article 246133). This
> >> stored procedure will generate a script on the source server that can be
> >> executed on the target server to create the logins with their original SIDs
> >> and passwords.
> >>
> >> "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
> >> news:ufoToIhsEHA.3320@.TK2MSFTNGP15.phx.gbl...
> >> > I think this is done for security reasons. The passwords after transfer
> >> > gets encrypted that way SA or equivalent user will reset the passwords.
> >> > Thereby implicit privileges will not be available to the login without the
> >> > administrator's knowledge.
> >> >
> >> > Bhanu.
> >> >
> >> >
> >> > "Ann" <Ann@.discussions.microsoft.com> wrote in message
> >> > news:4446F314-A347-4EBC-B621-798CFE959B12@.microsoft.com...
> >> > > We have a testing server. Sometimes we need to move some logins and
> >> users
> >> > > from production server to the testing server, I use transfer login or
> >> copy
> >> > > objects dts,
> >> > > Then some of our web pages cannot be acceessed by the logins.
> >> > >
> >> > > And I checked there was no orphaned users.
> >> > >
> >> > > Finally found the password is changed while transfer, change it to
> >> > oringinal
> >> > > password, then it works.
> >> > >
> >> > > My question is why the password changed during transfer or moving logins
> >> > or
> >> > > users?
> >> > > We didn't change it manually at all.
> >> > >
> >> > > Is this a bug ? or is this a problem many users have?
> >> > >
> >> > > Thanks
> >> >
> >> >
> >>
> >>
> >>
>
>|||That refers to the DTS method, not the stored procedure method. And, yes for Windows accounts, you
need to edit the file so you get the correct machines name in the account name.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ann" <Ann@.discussions.microsoft.com> wrote in message
news:AE8D5328-1991-4BED-946D-4430111A3656@.microsoft.com...
> Here is what I copied from the article, two paragraphs:
>
> Important The SQL Server 2000 destination server cannot be running the
> 64-bit version of SQL Server 2000. DTS components for the 64-bit version of
> SQL Server 2000 are not available. If you are importing logins from an
> instance of SQL Server that is on a separate computer, your instance of SQL
> Server will must be running under a Domain Account to complete the task.
>
> Remarks
> . Review the output script carefully before you run it on the destination
> SQL Server. If you have to transfer logins to an instance of SQL Server in a
> different domain than the source instance of SQL Server, edit the script
> generated by the sp_help_revlogin procedure, and replace the domain name with
> the new domain in the sp_grantlogin statements. Because the integrated logins
> granted access in the new domain will not have the same SID as the logins in
> the original domain, the database users will be orphaned from these logins.
> To resolve these orphaned users, see the articles referenced in the following
> bullet item. If you transfer integrated logins between instances of SQL
> Servers in the same domain, the same SID is used and the user is not likely
> to be orphaned.
>
>
> "Tibor Karaszi" wrote:
>> > Second I know this stored procedure, it says it works in only domain
>> > environment,
>> I didn't find such a statement in the KB. Can you elaborate?. And, domain structure is totally
>> irrelevant for SQL server logins.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Ann" <Ann@.discussions.microsoft.com> wrote in message
>> news:19E05F0B-57C7-41A3-8087-CF0749C6DE15@.microsoft.com...
>> > First, actually we have a udl file that we know what the passwords are.
>> > The file works good on the production server, but not the testing server,
>> > where we moved to. After I changed the password according to it on the
>> > testing server, it works. So there is no issue about encrypt and reset. I do
>> > user sa or sys admin account to do all the transfers.
>> >
>> > Second I know this stored procedure, it says it works in only domain
>> > environment, while ours is in workgroup enviroment, so guess not work for us.
>> > The dba here said we don't want to put sql servers in public domain for
>> > security reasons.
>> >
>> > So really want to know if this is a bug or other users may have this problem
>> > too?
>> > I mean after transfering log in, password changed'
>> > Thanks
>> >
>> >
>> >
>> >
>> >
>> >
>> > "TomB" wrote:
>> >
>> >> You may avoid this problem by using the master..sp_help_revlogin stored proc
>> >> available from Microsoft (reference Knowledge Base Article 246133). This
>> >> stored procedure will generate a script on the source server that can be
>> >> executed on the target server to create the logins with their original SIDs
>> >> and passwords.
>> >>
>> >> "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
>> >> news:ufoToIhsEHA.3320@.TK2MSFTNGP15.phx.gbl...
>> >> > I think this is done for security reasons. The passwords after transfer
>> >> > gets encrypted that way SA or equivalent user will reset the passwords.
>> >> > Thereby implicit privileges will not be available to the login without the
>> >> > administrator's knowledge.
>> >> >
>> >> > Bhanu.
>> >> >
>> >> >
>> >> > "Ann" <Ann@.discussions.microsoft.com> wrote in message
>> >> > news:4446F314-A347-4EBC-B621-798CFE959B12@.microsoft.com...
>> >> > > We have a testing server. Sometimes we need to move some logins and
>> >> users
>> >> > > from production server to the testing server, I use transfer login or
>> >> copy
>> >> > > objects dts,
>> >> > > Then some of our web pages cannot be acceessed by the logins.
>> >> > >
>> >> > > And I checked there was no orphaned users.
>> >> > >
>> >> > > Finally found the password is changed while transfer, change it to
>> >> > oringinal
>> >> > > password, then it works.
>> >> > >
>> >> > > My question is why the password changed during transfer or moving logins
>> >> > or
>> >> > > users?
>> >> > > We didn't change it manually at all.
>> >> > >
>> >> > > Is this a bug ? or is this a problem many users have?
>> >> > >
>> >> > > Thanks
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>|||Thanks. Yes, I mistook the first paragraph.It is for dts transfer.
But how about the second paragraph, or does it mean there is no problem
using the stored procedure in our workgroup enviroment.
Second I'm just curious, for dts transfer logins, did any users have the
same problem, that passwords are changed?
Thanks
"Tibor Karaszi" wrote:
> That refers to the DTS method, not the stored procedure method. And, yes for Windows accounts, you
> need to edit the file so you get the correct machines name in the account name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ann" <Ann@.discussions.microsoft.com> wrote in message
> news:AE8D5328-1991-4BED-946D-4430111A3656@.microsoft.com...
> > Here is what I copied from the article, two paragraphs:
> >
> >
> > Important The SQL Server 2000 destination server cannot be running the
> > 64-bit version of SQL Server 2000. DTS components for the 64-bit version of
> > SQL Server 2000 are not available. If you are importing logins from an
> > instance of SQL Server that is on a separate computer, your instance of SQL
> > Server will must be running under a Domain Account to complete the task.
> >
> >
> > Remarks
> > . Review the output script carefully before you run it on the destination
> > SQL Server. If you have to transfer logins to an instance of SQL Server in a
> > different domain than the source instance of SQL Server, edit the script
> > generated by the sp_help_revlogin procedure, and replace the domain name with
> > the new domain in the sp_grantlogin statements. Because the integrated logins
> > granted access in the new domain will not have the same SID as the logins in
> > the original domain, the database users will be orphaned from these logins.
> > To resolve these orphaned users, see the articles referenced in the following
> > bullet item. If you transfer integrated logins between instances of SQL
> > Servers in the same domain, the same SID is used and the user is not likely
> > to be orphaned.
> >
> >
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> > Second I know this stored procedure, it says it works in only domain
> >> > environment,
> >>
> >> I didn't find such a statement in the KB. Can you elaborate?. And, domain structure is totally
> >> irrelevant for SQL server logins.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Ann" <Ann@.discussions.microsoft.com> wrote in message
> >> news:19E05F0B-57C7-41A3-8087-CF0749C6DE15@.microsoft.com...
> >> > First, actually we have a udl file that we know what the passwords are.
> >> > The file works good on the production server, but not the testing server,
> >> > where we moved to. After I changed the password according to it on the
> >> > testing server, it works. So there is no issue about encrypt and reset. I do
> >> > user sa or sys admin account to do all the transfers.
> >> >
> >> > Second I know this stored procedure, it says it works in only domain
> >> > environment, while ours is in workgroup enviroment, so guess not work for us.
> >> > The dba here said we don't want to put sql servers in public domain for
> >> > security reasons.
> >> >
> >> > So really want to know if this is a bug or other users may have this problem
> >> > too?
> >> > I mean after transfering log in, password changed'
> >> > Thanks
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >
> >> > "TomB" wrote:
> >> >
> >> >> You may avoid this problem by using the master..sp_help_revlogin stored proc
> >> >> available from Microsoft (reference Knowledge Base Article 246133). This
> >> >> stored procedure will generate a script on the source server that can be
> >> >> executed on the target server to create the logins with their original SIDs
> >> >> and passwords.
> >> >>
> >> >> "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
> >> >> news:ufoToIhsEHA.3320@.TK2MSFTNGP15.phx.gbl...
> >> >> > I think this is done for security reasons. The passwords after transfer
> >> >> > gets encrypted that way SA or equivalent user will reset the passwords.
> >> >> > Thereby implicit privileges will not be available to the login without the
> >> >> > administrator's knowledge.
> >> >> >
> >> >> > Bhanu.
> >> >> >
> >> >> >
> >> >> > "Ann" <Ann@.discussions.microsoft.com> wrote in message
> >> >> > news:4446F314-A347-4EBC-B621-798CFE959B12@.microsoft.com...
> >> >> > > We have a testing server. Sometimes we need to move some logins and
> >> >> users
> >> >> > > from production server to the testing server, I use transfer login or
> >> >> copy
> >> >> > > objects dts,
> >> >> > > Then some of our web pages cannot be acceessed by the logins.
> >> >> > >
> >> >> > > And I checked there was no orphaned users.
> >> >> > >
> >> >> > > Finally found the password is changed while transfer, change it to
> >> >> > oringinal
> >> >> > > password, then it works.
> >> >> > >
> >> >> > > My question is why the password changed during transfer or moving logins
> >> >> > or
> >> >> > > users?
> >> >> > > We didn't change it manually at all.
> >> >> > >
> >> >> > > Is this a bug ? or is this a problem many users have?
> >> >> > >
> >> >> > > Thanks
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

Monday, March 12, 2012

Logins transfer problem

logins transfer

logins transfer

Logins from 2000 to 2005

Friday, March 9, 2012

logins

Wednesday, March 7, 2012

Login Transfers

Monday, February 20, 2012

login password changed

login password changed

login password changed

sql login permissions

Blog Archive

About Me