Transfer Logins(etc) to a server with the same name
-
Let's say that we have a test environment that looks just like production.
Server1 was P2V'd and exists within an isolated test environment as Server1 (same IP and all).
I can see both, but get to the isolated test through a NAT'd address (my host file resolves it for me).
Whether I hit that server by ServerName or IP, I still can't get around the whole "The source server can not be the same as the destination server." so I can't transfer logins or jobs without scripting them.
I'd like to use SSIS or some other tools to automate this but I can't find a way around this.
Anyone have experience with getting around this?
Thanks
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter -
I'd recommend using MicroSoft's sp_help_revlogin instead; it simply produces a script you can run in a few seconds;
there's different versions, depending on SQL 2000 vs 2005 and up, so get it straight from the web site there.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks for the reply.
I'm aware of that option, and actually pursued it briefly.
How to transfer logins and passwords between instances of SQL Server
This article describes how to transfer the logins and the passwords between instances of Microsoft SQL Server 2005, of Microsoft SQL Server 2008, and of Microsoft SQL Server 2012 on different servers.
For more information about how to transfer the logins and the passwords between instances of other versions of SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:
246133 How to transfer logins and passwords between instances of SQL Server
It didn't capture the fact that a SQL Login was in the sysadmin role.
It also wouldn't be transferable to using the linked server or jobs copy functionality I'm looking for.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter -
yeah the server roles would need to be scripted as a separate step;
this is what I have saved in my snippets for doing exactly that, if it helps;
SELECT 'EXEC master.sys.sp_addsrvrolemember @loginame = ''' + SUSER_SNAME(r.sid) + ''', @rolename = ''' + p.name + ''';
'
FROM master.sys.server_principals r
JOIN master.sys.server_role_members m ON r.principal_id = m.member_principal_id
JOIN master.sys.server_principals p ON m.role_principal_id = p.principal_id;
To stick with SSIS, maybe you could create an Alias for the other server on your current machine in SQL server Configuration Manager, so the don't have the same name?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Yes, that is very helpful especially if I have to go the route of scripting it all.
I just tried the alias route, and it appears to do the same thing. I know it's by design, but there should be some way to compare 2 servers with the same name across environments.
That's the silver bullet I'm looking for.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply