Friday, March 23, 2012

Migrate user account to a different server

I need to migrate a user account to a different SQL Server. I can't just recreate it, becuase I don't know the password and can't change it because some applications are using it.

Is there a way to copy this user account and all of it's associated properties to a new server?

Any help is appreciated.

MikeI need to migrate a user account to a different SQL Server. I can't just recreate it, becuase I don't know the password and can't change it because some applications are using it.

Q1 Is there a way to copy this user account and all of it's associated properties to a new server?

Any help is appreciated.

Mike

A1 Generally, yes. You have not mentioned sufficient details to know for certain i.e.(standard logins or domain accounts, different domains, source and target versions etc.). Between 7.0 and / or 2k versions there are (at least) two ways that generally may work. Within and / or between newer and older versions (6.x, etc.) you may be able to selectively bcp out the applicable login data and then bcp in the output file into the target server(s)|||Yep, there was definitely more information that I could have provided. :)

Here goes:
SQL Server accounts, not NT accounts.
SQL Servers are in the same domain.
Both servers are SQL 2000 SP-2.|||Are any of the databases being transferred that require this login ? If not, then you can use the dts transfer logins task. If there are databases being transferred you will have to make sure the sids match up - the following article describes both scenarios:

article (http://support.microsoft.com/default.aspx?scid=kb;EN-US;246133)|||RE: Yep, there was definitely more information that I could have provided. :)
Here goes:
SQL Server accounts, not NT accounts.
SQL Servers are in the same domain.
Both servers are SQL 2000 SP-2.

That makes things fairly simple. You may choose to create a tsql script that you may edit and simply run on as many target servers as necessary. {Assuming completley new and different databas(es) are to be accessible by the same login on the target server(s), you will of course have to grant the new login any permissions / rights on the target database(s) on the target server(s)}.

To create such scripts you would need to create and use the stored procedures which are fully described in:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;246133 (as already noted by rnealejr).|||That is exactly what I needed, thanks guys!!!!

Don't know why I couldn't find that article... :D|||Happy to help.

Good luck.

No comments:

Post a Comment