Logins & password

I need to rebuild SQL Server from Enterprise Edition to Standard Edition.
Is there a way to save the passwords for all SQL logins (SQL
authentication)? I know that restoring master will get the password back
but does it defeat the purpose of changing the SQL Server edition? Thanks!This occur on the same server.
"Flicker" <hthan@.superioraccess.com> wrote in message
news:eI2znzXcDHA.2392@.TK2MSFTNGP10.phx.gbl...
> if you already have the std SQL box you can DTS to transfer logins.
> "Kevin" <kevin@.noemail.com> wrote in message
> news:eom3QuXcDHA.2820@.tk2msftngp13.phx.gbl...
> > I need to rebuild SQL Server from Enterprise Edition to Standard
Edition.
> > Is there a way to save the passwords for all SQL logins (SQL
> > authentication)? I know that restoring master will get the password
back
> > but does it defeat the purpose of changing the SQL Server edition?
> Thanks!
> >
> >
>|||HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q246133#4
Create the stored procedures in the KB article then run sp_help_revlogin in
Query Analyzer with results as text. You can then copy and paste the results
into Notepad and save it. Once you've rebuilt you can run the script to add
your logins with previous SID and passwords
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Kevin" <kevin@.noemail.com> wrote in message
news:eom3QuXcDHA.2820@.tk2msftngp13.phx.gbl...
I need to rebuild SQL Server from Enterprise Edition to Standard Edition.
Is there a way to save the passwords for all SQL logins (SQL
authentication)? I know that restoring master will get the password back
but does it defeat the purpose of changing the SQL Server edition? Thanks!

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,
MZeeshan
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!TK2MSF TNGXA03.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!TK2MSF TNGXA03.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!TK2MSF TNGXA03.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!TK2MSF TNGXA03.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!TK2MSF TNGXA03.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
| > | > |
| > | >
| > | >
| > |
| >
| >
|

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 SQ
L
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: "examnotes" <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 Serv
er
> 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: "examnotes" <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 transfe
r
> | 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 o
n
> 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: "examnotes" <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: "examnotes" <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: "examnotes" <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: "examnotes" <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: "examnotes" <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: "examnotes" <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: "examnotes" <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
| > | > |
| > | >
| > | >
| > |
| >
| >
|

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

login security question

Hi,
I have an application which connects to the SQL server. We have several users logging into this application. All of their user-id, passwords are validated and converted to an owner profile, which is then used throughout the application.
My problem is, this owner profile should be prevented from accessing the database directly using Enterprise Manager or Query Analyser. The database should be accessible only from the application for this owner/global profile.
How do I go about achieving this. The application was set up like this by a person long time back who is not with us anymore. Also, I do not know SQL Server Administration. So, please detail out what information I have to look up and what steps I will hav
e to follow.
Thank you in advance.
Sunny
Look into SQL Server Books online for "application roles" topic. This
explains about how to create and activate an application role within your
program and use it. This may require some code changes.
HTH
Prasad Koukuntla
"Sunanda" <Sunny@.discussions.microsoft.com> wrote in message
news:87CB3205-C041-4F23-AC6E-7BF23E7AB2C6@.microsoft.com...
> Hi,
> I have an application which connects to the SQL server. We have several
users logging into this application. All of their user-id, passwords are
validated and converted to an owner profile, which is then used throughout
the application.
> My problem is, this owner profile should be prevented from accessing the
database directly using Enterprise Manager or Query Analyser. The database
should be accessible only from the application for this owner/global
profile.
> How do I go about achieving this. The application was set up like this by
a person long time back who is not with us anymore. Also, I do not know SQL
Server Administration. So, please detail out what information I have to look
up and what steps I will have to follow.
> Thank you in advance.
> Sunny
|||In my opinion, that does not seem possible. SQL doesn't know what CLIENT TOOL is touching it. If the "connection" from the client application comes in through a username/password, then that username/password has access to SELECT, UPDATE, DELETE, etc fro
m tables.
That is why we do all our database access through STORED PROCEDURES - so actual table access is not possible. Granted, the users can still call STORED PROCEDURES from the EM and QA tools, but that is less likely to happen.
Can you hide the "connection" username/password from the users?
"Sunanda" wrote:

> Hi,
> I have an application which connects to the SQL server. We have several users logging into this application. All of their user-id, passwords are validated and converted to an owner profile, which is then used throughout the application.
> My problem is, this owner profile should be prevented from accessing the database directly using Enterprise Manager or Query Analyser. The database should be accessible only from the application for this owner/global profile.
> How do I go about achieving this. The application was set up like this by a person long time back who is not with us anymore. Also, I do not know SQL Server Administration. So, please detail out what information I have to look up and what steps I will h
ave to follow.
> Thank you in advance.
> Sunny
|||Steve,
No the connection profile is alreay know to the users, that is why we would like to prevent users from using that in the enterprise manager to make changes.
Please let me know if there are any options.
Thanks a lot.
Sunny
"Steve Z" wrote:

> In my opinion, that does not seem possible. SQL doesn't know what CLIENT TOOL is touching it. If the "connection" from the client application comes in through a username/password, then that username/password has access to SELECT, UPDATE, DELETE, etc f
rom tables.[vbcol=seagreen]
> That is why we do all our database access through STORED PROCEDURES - so actual table access is not possible. Granted, the users can still call STORED PROCEDURES from the EM and QA tools, but that is less likely to happen.
> Can you hide the "connection" username/password from the users?
> "Sunanda" wrote:
have to follow.[vbcol=seagreen]
|||"Sunanda" <Sunny@.discussions.microsoft.com> wrote in message
news:87CB3205-C041-4F23-AC6E-7BF23E7AB2C6@.microsoft.com...
> Hi,
> I have an application which connects to the SQL server. We have several
users logging into this application. All of their user-id, passwords are
validated and converted to an owner profile, which is then used throughout
the application.
> My problem is, this owner profile should be prevented from accessing the
database directly using Enterprise Manager or Query Analyser. The database
should be accessible only from the application for this owner/global
profile.
> How do I go about achieving this. The application was set up like this by
a person long time back who is not with us anymore. Also, I do not know SQL
Server Administration. So, please detail out what information I have to look
up and what steps I will have to follow.
If you can alter the code in the client application, you can use application
roles.
1) Use Enterprise Manager to access the database / roles. New Role. click
the Application Role radio button and give it a nice secure, obscure
password.
2) Give the Application Role the appropriate permissions.
3) Revoke the users' permissions
4) in the code of the application, put in a call to a stored procedure
called (I think, from memory) sp_setAppRole (F1 for application role to see
what the stored proc is called) using the secret password for the App Role
(which you don't share with the end users).
Now your users will have the appropriate permissions when using your app,
but not when using QA or any other app.
On an entirely different tack, you can try Group Policies. Use a GP to tie
down their desktop so that they are not allowed to run Quey Analyzer or
Enterprise Manager.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.711 / Virus Database: 467 - Release Date: 25/06/2004
|||Bob,
Thanks for your reply. I kinda understand this Application Role approach. Could you please explain the following:
1. Say the password for the Application role is found, can a user access the database through the Query Analyser or Enterprise Manager using the application rolde/password.
2. At present the application tracks the user who makes the changes to the database through the application. The application passes the userid to the stored procedures. But if I put in a Applciation role in between, will I still have the actual userid to
track who actually did the inserts and updates through the front-end.
Thanks in advance,
Sunanda.
"Bob Simms" wrote:

> "Sunanda" <Sunny@.discussions.microsoft.com> wrote in message
> news:87CB3205-C041-4F23-AC6E-7BF23E7AB2C6@.microsoft.com...
> users logging into this application. All of their user-id, passwords are
> validated and converted to an owner profile, which is then used throughout
> the application.
> database directly using Enterprise Manager or Query Analyser. The database
> should be accessible only from the application for this owner/global
> profile.
> a person long time back who is not with us anymore. Also, I do not know SQL
> Server Administration. So, please detail out what information I have to look
> up and what steps I will have to follow.
> If you can alter the code in the client application, you can use application
> roles.
> 1) Use Enterprise Manager to access the database / roles. New Role. click
> the Application Role radio button and give it a nice secure, obscure
> password.
> 2) Give the Application Role the appropriate permissions.
> 3) Revoke the users' permissions
> 4) in the code of the application, put in a call to a stored procedure
> called (I think, from memory) sp_setAppRole (F1 for application role to see
> what the stored proc is called) using the secret password for the App Role
> (which you don't share with the end users).
> Now your users will have the appropriate permissions when using your app,
> but not when using QA or any other app.
> On an entirely different tack, you can try Group Policies. Use a GP to tie
> down their desktop so that they are not allowed to run Quey Analyzer or
> Enterprise Manager.
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.711 / Virus Database: 467 - Release Date: 25/06/2004
>
>
|||<<1. Say the password for the Application role is found, can a user access the database through the
Query Analyser or Enterprise Manager using the application rolde/password.>>
Yes.
<<2. At present the application tracks the user who makes the changes to the database through the
application. The application passes the userid to the stored procedures. But if I put in a
Applciation role in between, will I still have the actual userid to track who actually did the
inserts and updates through the front-end.>>
Yes. You can see the login id for the users, and you can use the SYSTEM_USER function in, for
example, a trigger to get the login name. The user name, however, will be the app role name.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sunanda" <Sunanda@.discussions.microsoft.com> wrote in message
news:00D6F5D5-0BC3-43A4-A313-649B5E2E215C@.microsoft.com...
> Bob,
> Thanks for your reply. I kinda understand this Application Role approach. Could you please explain
the following:
> 1. Say the password for the Application role is found, can a user access the database through the
Query Analyser or Enterprise Manager using the application rolde/password.
> 2. At present the application tracks the user who makes the changes to the database through the
application. The application passes the userid to the stored procedures. But if I put in a
Applciation role in between, will I still have the actual userid to track who actually did the
inserts and updates through the front-end.[vbcol=seagreen]
> Thanks in advance,
> Sunanda.
>
> "Bob Simms" wrote:
|||Thanks for your reply.
But can't the application role be restricted from using the QA/EM? This is not a completely secure method. Is there any alternative.
Thanks,
sunanda.
"Tibor Karaszi" wrote:

> <<1. Say the password for the Application role is found, can a user access the database through the
> Query Analyser or Enterprise Manager using the application rolde/password.>>
> Yes.
>
> <<2. At present the application tracks the user who makes the changes to the database through the
> application. The application passes the userid to the stored procedures. But if I put in a
> Applciation role in between, will I still have the actual userid to track who actually did the
> inserts and updates through the front-end.>>
> Yes. You can see the login id for the users, and you can use the SYSTEM_USER function in, for
> example, a trigger to get the login name. The user name, however, will be the app role name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Sunanda" <Sunanda@.discussions.microsoft.com> wrote in message
> news:00D6F5D5-0BC3-43A4-A313-649B5E2E215C@.microsoft.com...
> the following:
> Query Analyser or Enterprise Manager using the application rolde/password.
> application. The application passes the userid to the stored procedures. But if I put in a
> Applciation role in between, will I still have the actual userid to track who actually did the
> inserts and updates through the front-end.
>
>
|||> But can't the application role be restricted from using the QA/EM?
No, that is not the way it work. You need to protect the password. Why do you say it is not a secure method?
Are you afraid of network sniffing? There's an encryption option in sp_setapprole.

> Is there any alternative.
I don't know what your requirements are, as I haven't read the full thread. App roles is a nice feature for
what it is performing. Other options includes app logins using a special password (but all users will use the
same logins), app uses stored procedures and views to access data...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sunanda" <Sunanda@.discussions.microsoft.com> wrote in message
news:CA998A64-A51E-42B4-9B8D-E06C37624B65@.microsoft.com...
> Thanks for your reply.
> But can't the application role be restricted from using the QA/EM? This is not a completely secure method.
Is there any alternative.[vbcol=seagreen]
> Thanks,
> sunanda.
>
> "Tibor Karaszi" wrote: