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...
users[vbcol=seagreen]
copy[vbcol=seagreen]
> oringinal
> or
>|||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 pr
oc
> 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 SID
s
> and passwords.
> "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
> news:ufoToIhsEHA.3320@.TK2MSFTNGP15.phx.gbl...
> users
> copy
>
>|||> 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 st
ructure 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...[vbcol=seagreen]
> 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 probl
em
> too?
> I mean after transfering log in, password changed'
> Thanks
>
>
>
> "TomB" wrote:
>|||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 wit
h
the new domain in the sp_grantlogin statements. Because the integrated login
s
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 followin
g
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:
> 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...
>
>|||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 na
me.
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...[vbcol=seagreen]
> 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 o
f
> 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 SQ
L
> 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 w
ith
> the new domain in the sp_grantlogin statements. Because the integrated log
ins
> 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 follow
ing
> 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 likel
y
> to be orphaned.
>
>
> "Tibor Karaszi" wrote:
>|||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 f
or 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...
>
>
No comments:
Post a Comment