Copying Logins To A New Server
Aug 21, 2002
I have succeeded in reattaching the db to the new server, but the old logins do not work, as has been experienced by many people.
What follows was is a solution from an earlier post (last year) on this topic. I tried running the queries from Master in on the new server, and got the following error:
Server: Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 'sysusers' with unique index 'sysusers'.
The statement has been terminated.
The sql queries I am using are:
insert sysxlogins(srvid, sid, xstatus, xdate1, xdate2, name, password, dbid, language)select srvid, sid, xstatus, xdate1, xdate2, name, password, dbid, language
from "oldServerName".master.dbo.sysxlogins
where name not in ('DISTRIBUTOR_ADMIN', 'REPL_PUBLISHER', 'REPL_SUBSCRIBER', 'TRACEUSER', 'SA')
AND
insert sysusers(uid, status, name, sid, roles, createdate, updatedate, altuid, password)
select uid, status, name, sid, roles, createdate, updatedate, altuid, password
from "oldServerName".master.dbo.sysusers
where name not in ('public', 'INFORMATION_SCHEMA', 'db_owner', 'db_accessadmin', 'db_securityadmin', 'db_ddladmin', 'db_backupoperator', 'db_datareader','db_datawriter', 'db_denydatareader', 'db_denydatawriter', 'guest', 'dbo')
The old server is set up as a linked server on the new server.
This suggests that the information is all in the new server. Any tips? TIA D. Lewis
View 2 Replies
ADVERTISEMENT
May 6, 2002
Hello List,
Folks, I need a help here. We are running our production database on SQL Server 7.0. We are in a process of switching the production database server to another server hardware. I was wondering, How can we copy the Logins/Passwords from my old server to the new one. Well this is very important to have all the old logins and passwords on the new server to have anybody login to it. I know, Doing Export will copy the logins. But would it allow the same old password to be on the new server?
Please help me here.
View 1 Replies
View Related
Jul 17, 2001
Hello,
I would move a Database to another server. I try to use DTS but I have problems with this process because DB have big tables, I think. I try to use DETACH and ATTACH procedures but logins doesn't export. And more, in new server there are already logins from another DBs.
What's the best way to solve this problem?
Please, help
Thanks
View 3 Replies
View Related
Apr 3, 2007
I am a systems analyst and work with an app that runs against 2 SQL Server DBs. Though I have some familiarity with SQL Server and SQL, I am not a DBA.
The app executable is tied to a Windows service.
When we install the app, we run a process that builds 2 dbs to include:
Tables, indexes, stored procedures, views and user accounts.
SQL Server is set up for mixed mode authentication.
Normally, the dbs run off the local db user accounts which are tied to local logins with the same names.
We have a client that wants to remove our standard logins so that they can run on only a Windows login.
I know I should be able to tie the db users to a Windows login.
And I can do the same for the service.
But I am at a loss as to how to get this done.
How do you associate db users with a Windows login?
When I have tried sp_change_users_login I get an error that the Windows login does not exist. (Though I have added the Windows account to the DB.)
Hope this all makes sense.
View 2 Replies
View Related
Jan 21, 2007
ok, first, I know... I forgot to run a backup of the master database, and I forgot to run a script to caputure logins. Not that that is out of the way... I need to recreate the logins under the Securities tab below the databases. All the company databases have the user names and passwords assigned to them, but they are not able to login, because they are not able to authenticate to the SQL server first.
Is there a script that someone has that will copy the company database security info for the users and recreate them in the SQL security tab?
I know that I can rebuild them manually, but I need to delete them first in the application software, then delete them from the databases, and then recreate them in the application software... and as simple as that sounds... it is a slow moving process.
Any assistance would be greatly appreciated.
Thanks,
John
View 3 Replies
View Related
Oct 17, 2000
good afternoon,
Does any1 know how to prevent a login screen being encountered upon hitting the SQL dbase? I'm sure there is a table in SQL server that you can create that allows this (and the number of rows retrieved as well). Can any1 help me out?
Thanks in advance for any and all help
gurmi
View 3 Replies
View Related
Sep 10, 2001
We are in process of migrating of databases from one Server to another, Can anyone please suggest me how to transfer or copy the logins from one Server to the other instead of creating them all over again??
Thanks in advance...
View 1 Replies
View Related
Sep 13, 2001
Hi,
Can anyone help me to script sql server logins.
Thanks,
ravi.
View 2 Replies
View Related
Dec 12, 2001
I need to restore the logins from prod server "A" to a dev server. The dev server has many databases from different prod servers. If I restore the master from prod A to dev, i'll lose the logins on dev for the other dbs. How can I get the logins from prod A to dev without losing the other logins for the other dbs?
Your help is greatly appreciated!
View 1 Replies
View Related
May 7, 2008
Can someone remind me where Server Logins are stored in SQL Server?
View 7 Replies
View Related
Oct 26, 2005
I have an application that runs off an sql server 2000 sp3 database. I have taken a backup (.bak file) of the production database and restored it to another server for testing purposes. the users cannot login to the new server because the logins have not been transfered to the new server. I am looking to transfer the logins using the procedure outlined at the following link: http://support.microsoft.com/kb/246133/ under the header "Create and Run Stored Procedures in the Master Database". what I need to know is will this procedure affect the production server in any way? (I can't have that) and is there anything else I need to do after this to let the users login to the test server with the same name and passwords?
thanks for any help
View 4 Replies
View Related
Sep 18, 2006
I need to create a new login that only has access to 2 Views.
I have tried everything, A New Role, Schema, set the Securables but when I connect to the server with MS Access or MS Excel, and sign in using that Login, I can still a bunch of tables. I only want this Login to be able to run either view and not see anything thing else.
View 13 Replies
View Related
Apr 1, 2006
Historically, many of us have used bulk copy to move selected contents of syslogins from one server to another so that the logins stayed in sync for things such as replication, log shipping, etc.
In SQL 2005, I have no access to passwords (this is proper from a security perspective) so I can't generate a file with bulk copy. I also can't update sys.server_principals, so it doesn't really matter that I can't export the passwords.
So what is the SQL 2005 solution? Suppose I decide to mirror my database. How do I get the logins that are users of that database over to the mirror server? Scripting the logins won't work because it generates a random password. sp_change_users_login requires me to provide a password but then I would have to know each login's current password or give them each a new password. This is viable with 2 users; it is not viable with 2000!
I know that Copy Database Wizard will move the logins with the passwords intact, but it will assign new SIDs to SQL Server authentication logins. That's fine as long as it is a one-time move but not if I am maintaining a warm standby.
This problem has been one of the most frequent problems posted on newsgroups, etc. in the past. But we have had solutions. I fear that we do not have one in SQL 2005.
I'd appreciate any help you can provide.
Thanks,
Sharon
View 4 Replies
View Related
Nov 30, 2005
I am replacing a server so I need to migrate everything. The old server is running SQL2000 and the new server is running SQL2005. I am trying to write an SSIS solution to migrate everything for me and I can't even get started because I get the error "The source server can not be the same as the destination server". At the same time I am changing the name of the Domain so the two machines arenot even members of the same Domain. I am doing this over the Internet so the machines are not even on the same subnet. The only thing I can think of is that the machine names are the same so even though the domains are different therefore the full names are different, the NetBIOS names are the same. Could that be the problem?
View 1 Replies
View Related
Dec 4, 2006
Hi guys,
I have tried to use the sp_help_revlogin script to move logins from a sql 2000 server to a sql 2005 server. It works for users that have a password. But if the password is blank it will fail. Is there a work around for this since I can't change the users passwords?
I have also tried SSIS package but that too doesn't work because of the same reason.
Can anyone help...
View 1 Replies
View Related
Apr 12, 2000
I need to move several databases to a new server while retaining the same logins/permissions. Books Online indicates that DTS can move the SQL Server logins, but it sets the passwords to NULL in the process. Is there any way to move the logins and keep all passwords/permissions intact?
View 2 Replies
View Related
Mar 27, 2004
I'm changing servers and want to copy all the logins from the old server to the new server. Is there anyway to do this?
View 5 Replies
View Related
Jan 10, 2007
I am developing an application in VB.NET that is using a sql database. I am trying to start testing with multiple computers running the app while the database is on a remote computer. I have 2 desktop computers networked right now with 1 of them having the database and the other I want to run the program on. I am trying to figure out how to use windows authentication to login to sql server from the remote computer. I can login using Sql authentication but it is taking forever and I keep getting timeout errors so I wanted to try Windows Authentication to see if it is faster. If someone could walk me through the steps I need to take or give me some suggestions to fix my problems I would greatly appreciate it.
View 2 Replies
View Related
Jun 25, 2007
I have SQL Server 2005 on a DEV box and I have a linked server on that box.
It has several logins that I would like to move over to our production server when I re-create the linked server.
Now I can export the script to create that linked server, but when I do so it doesn't provide me with the user's login password.
Is there a way to export the password as part of the script so I can move the linked server AND all the users associated to it over to a new server?
View 1 Replies
View Related
Nov 1, 2007
Sometimes a user name will change in Active Directory and I have to change the name in SQL Server as well. So what I do is first delete the old user name from SQL Server's logins. However I wont be able to see the new user name in SQL Server to add it as a legit user until I roboot the server. Is there somekind of code I can execute to resynchronize with Active Directory again?
View 5 Replies
View Related
Jul 20, 2005
Hello,Can anyone tell me how to monitor logins/logouts to Analysis Servicesdatabases? I use Profiler to do so in SQL, but cannot find a way to doit in AS.Thanks,Tim*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
May 2, 2007
We are moving from Oracle to SQL Server 2005 for our next release and I'm looking for content that describes creating Logins, creating User accounts and what approach to take if the database is using windows authentication vs. SQL authentication.
Any suggestions?
View 1 Replies
View Related
Apr 13, 2006
We want to merge 2 SQL servers into one. This means we will have duplicate logins with differing passwords.
We want to reset all the passwords on one server to something memorable. Does anyone have a script that cursor through (or trans - I'm not shy) all the logins in a mster db? I really don't fance going through hundreds of logins changing their passwords by hand!
View 1 Replies
View Related
Mar 28, 2006
I€™m using express edition of SQL server 2005. 9.00.1399.06 (Intel X86)
I need to transfer all SQL logins and passwords from one server to another.
The script at http://support.microsoft.com/kb/246133 (designed for sqlserver 7.0 and 2000) This script references a system table (master..syslogins) that may not exits in sqlserver 2005. I get the following error:
EXEC master..sp_help_revlogin
Msg 208, Level 16, State 1, Procedure sp_help_revlogin, Line 12
Invalid object name 'master..sysxlogins'.
Would SMO be something to try for transfering logins and passwords?
Thanks,
Karl
View 3 Replies
View Related
Sep 10, 2007
Haven't been able to find the answer to this after sever searches. So decided to open the thread myself.
I am trying to write a report enumerating logins in SQL Server and all database-level and object-level permissions granted to each login.
Can someone tell me what system objects I can query to fetch this information?
TIA.
View 3 Replies
View Related
Feb 15, 2005
I was trying to find the fastest way to COPY a 50G DB from our production server to our test server. I was testing this on our test server and had a simple question.
Attach/Detach is out since I can only move not copy a DB using this method.
export/import, bcp and DTS take too long and are not indented for this anyways.
so, I was left with Backup and restore. so, I tried that on two different test servers. Took very long. so, I decided to be brave and try the following.
1. Take DB1 that's running on server1 OFFLINE
2. Manually copy all datafiles and logfiles from Server1 to server2.
3. Attach DB1 on server2.
It came up great. No complaints whatsoever. So basically, it's the same as attach/detach but instead of detaching the DB I took the Db offline and copied the files over. So my question...
Is this supported?
Any chance of corruption on either of the servers?
If a DB is offline, is copying datafiles and logfiles supported (I am bit worried if I might corrupt data in production).
Do people use this method to COPY databases or do they stick with BACKUP and RESTORE?
PS: Are there any other ways to do this?
Thanks so much.
View 5 Replies
View Related
Feb 16, 2006
Just installed Sql Server Express, trying to figure out how to copy a database. I'm pretty new to databases in general.
Let's say I've created a new database called test1. Now, I want to create an identical copy (data not important, I just need the table structures, keys, constraints, etc.) called test2. Not sure how I would go about doing that. I see that import/export functionality is disabled in the Express version, if that's what I would have needed. I tried copying the mdf file manually and calling "attach" - that failed. Any suggestions? Thanks!
View 2 Replies
View Related
Mar 5, 2008
This question is about SQL Server 2005:
I have been trying to figure out how to copy tables and stored procedures between 2 databases (on the same server) using SQL Server Management Studio. I have tried right clicking on the table name, "script table as", "drop to", "clipboard", then I click on the 2nd database, and then click on the "tables" . I change the name of the database and click "execute". This creates the table but does not copy the data. I have also tried "create to" "clipboard" and "insert to" "clipboard" and cannot seem to be able to figure out how to get the results that I want. I am new at this but need to get the tables with the data copied along with the stored procedures, even if I have to do them one at a time. When I was using SQL Server 2000, I was able to use DTS to copy objects to other databases easily. Can someone please tell me a way to accomplish what I need to do? I have gotten information here before that was very useful and was hoping that someone can help me again.Thank you so much. Carol Quinn
View 9 Replies
View Related
Apr 13, 2007
I have created a site using VWDExpress and now that I’ve finished testing have moved it over to the server which runs SQLServer 2000. Part of the site requires login, so I created the membership using the ASP.net web configuration tool and when testing locally worked well.
Now though that I’ve copied the web site over, when I try to log in I get the error:
System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified
Obviously its some sort of configuration issue, but I don’t know what.
What do I have to change to make this work from a test machine to a live server?
Thanks
View 19 Replies
View Related
Aug 27, 2007
Hi,
This thread is a reformulation of a prior thread.
I created a login 'Network service' at server level in Management Studio express.I use windows authentification.Then i defined an user for my database which is associated to login 'Network service', because the application asp.net uses that account (IIS 6.0). This user received db_read and db_write roles.This works.Now i experimented a little bit and i removed from the logins at server level the login 'Network service'.Result: the application still works..Then i removed the Builtinusers login from the login list at server level.Result: i get the error: "login failed for Network service".I recreated then the login 'Network Service' at server level but not the Builtinusers login.Result: it works again.My conclusion is: one of the two logins must be in the list: Network Service or BuiltinusersIs this right?Why do i get that error when both logins are removed and not only when Network Service is removed?Thanks
View 1 Replies
View Related
Apr 8, 2002
Hi I took a Full Backup on Production Server and Restored the Same Database on Testing Server, i am able to see all the Tables Stored Procedure and View except Logins, i am not able to see any logins, there are total 650 Logins under the Database how can i see the logins and how to restore the logins, while i am trying to create any logins it is saying Error Occured login is already existing in the Database..
Bye,
Madhavi
View 3 Replies
View Related
Feb 13, 2015
How to find the list of logins/users who do not have any permission (except default Public) access.
View 4 Replies
View Related
Jun 24, 2015
How to get all logins & users with their password into other server while doing migration?
View 7 Replies
View Related