We have a "spanking new" machine to run SQL server and moving the databases over was absolutely no problem.
However, we discovered that we left the user ids and passwords behind.
Is there a way to get them copied from the old database to the new one?
When using the script to transfer logins and passwords from one server to another. After doing so... Does the logins and passwords still appear on the server transfering from??? I am doing a parallel install and need to keep the production database logins and passwords still intact. Thanks...
I did a detach on each database/logs.
Moved to the exact location on the new server.
HiI need to move a couple of SQL logins to a new machine to support a3rd party application. Problem is the password is embedded in theapplication and we can't see how to change it. Does anyone have amechanism for moving a login to a new machine *without* knowing orchanging the password?TIAChloe
View 1 Replies View RelatedI 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 RelatedI have a developer who created a DTS package using an NT authenticated ID. Now that we've migrated the SQL Server to another Domain in Active Directory, I am showing the following error for his jobs:
"The job failed. Unable to determine if the owner (DomainUSer) of job EmployeeDump has server access (reason: Could not obtain information about Windows NT group/user 'DomainUSer'. [SQLSTATE 42000] (Error 8198)).
When I went to check and see what the permissions problem might be, I noticed that all of my NT Authenticated ID are broken after migration - in other words, I can add the Active Directory NT ID's to the SQL Server instance, but I cannot give these ID's permissions to databases because it says they already exist??? I know if I have a sql login that is 'orphaned', I can use "sp_change_users_login" to fix it, but I think I read that this won't work for NT authenticated IDs.
IS there a way to fix this short of dropping ALL of my NT authenticated logins and recreating them????????
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 RelatedHey. I've a script which gets the SQL usernames with the information I need. Is it possible to migrate the passwords also with this information?
Also, regarding Windows Logins, do i have to worry about their SIDS when migrating to SQL 2005. I'm going to attach SQL 2000 db's onto a 2005SQL instance and these db's alreayd have the usernames assigned in them. Please let me know if there is a way out for the above two questions. thank you.
SELECT 'Create LOGIN [' + loginname + '] with password = ''hello'', sid = ',sid,
', default_database = ' + dbname + ', default_language = ' + language + ',check_expiration=off,check_policy=off'
FROM master..syslogins
WHERE name NOT IN ('sa')
AND isntname = 0
<g class="gr_ gr_78 gr-alert gr_gramm Punctuation replaceWithoutSep" data-gr-id="78" id="78">Friends,</g>
<g class="gr_ gr_78 gr-alert gr_gramm Punctuation replaceWithoutSep" data-gr-id="78" id="78">I</g>
i am migrating one database from SQL Server 2008 where I have around 20 databases to SQLServer 2012. To migrate the login I was thinking about to use SP_Help <g class="gr_ gr_114 gr-alert gr_spell ContextualSpelling ins-del multiReplace" data-gr-id="114" id="114">revlogin</g> store procedure but now what I believe is this SP scripts out whole server level login and I don't need all the logins in the server except related to the database that I am migrating to.
<g class="gr_ gr_418 gr-alert gr_gramm Grammar multiReplace" data-gr-id="418" id="418">please</g>
best way to migrate logins only related to the specific database to the new server.
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
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
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.
Help!!!!!!
I cannot get the SQL Server Upgrade Wizard to work!!
I have one machine with NT 4 (SP4) and SQL 6.5 and another machine with NT 4 (SP4) and SQL 7.0 (new install, never had SQL 6.5 one it). I want to upgrade a database to 7.0.
Each machine can 'see' the other via shares and even trying to register a SQL database in each other machines Enterprize manager (although that fails because they are different versions of SQL)
Using the Upgrade Wizard, on the third screen, where you specify the Server Name for the export server, it even has the other server's name in the dropdown list {I never even put it in}. And yet, after I set the export server name and put in the database SA account password and then put in the SA account password for the local server (the one that is running the Upgrade Wizard).
After I select next, I get a message that states...
'Unable to connect to Export Server. Please verify that you are an NT administrator on that machine.'
These are standalone servers. Not BDC's. I am logged into each machine as the administrator.
I have tried adjusting the SQL services to allow logging as the System Account and the administrator account and a whole bunch of options....
but I can't get it to work.
HELP!!! This shouldn't be this hard.... should it???
Does creating the tables first before copying the data from 6.5 to 7.0 is the best way to migrate from SQL 6.5 to 7.0?
Thanks in Advance!
I run SQL 6.5 on an NT 4.0 platform in a client/server environment with Windows 95 as the clients. I use VB 6.0 as my user interface with Access 97 utilizing DAO (yes, still DAO) for the ODBC connection to the SQL server. When I install SQL 7.0 and move my data to it, can I refresh my links in Access 97 from ODBC to OLE DB and automatically take advantage of the new connection, or do I have to rewrite my queries to take use OLE DB rather than DAO? Thanks for any suggestions and help.
View 2 Replies View RelatedHello People,
I currently have a MS Access database that will eventually be migrated to MS SQL Server. There won't be an application now, just the database and the reports. My doubts are on how easy this transition will be.
1 - After I build all the database with tables and relationships, is there a tool that helps me to import data?
2 - Is there a way to insert the data on the database other than writing the SQL codes, I mean maybe creating some 'forms'?
3 - Once the data is in, it there a way for me to create report on the SQL Server itself or do I need a report generator like Cristal Reports for example;
4 - If yes, then how easy it is to integrate them;
5 - Is it complicated to generate these reports or it is pretty much the same interface as in Access or Delphi?
Thanks a lot
Atha
I have an ms sql 2000 db which I am migrating from one shared environment to another. How specifcally can I do this?
I have a copy of enterprise but not a full version of SQL on my local desktop.
The hosting company from which I am moving has placed the db as a zip file on the root of my site via ftp. I have unzipped it and it appears as a backup copy (.bak).
I appreciate any detail steps, I assume via enterprise, that anyone can give me. Thank you.
I am very new to SQL so please bear with me. I have an SQL 2000 server running on Windows 2000 server in an NT4.0 Domain. We are planning to migrate (not upgrade!) everything to a new WIndows 2003 AD environment using ADMT. I wanted to know if there are any issues with the migration of an SQL server if we are to move forward with this strategy. I have heard that an SQL database keeps domain related information within its database and wanted to know if this is true.
Thank you
Hi,
I need to migrate db,Im planning to use attach & detach option.Is there any cons in migrating the databases(32 bit,not clustered ,sql 2005) to databases(64 bit,clustered,sql 2005).What are the things do i need to take care of?what are the things do i need to do after the migration?
Thank
How do you make Asp.net use something other than the local ASPNET user for data access in sql server
View 3 Replies View RelatedI need to copy 80 logins within the same SQL Server (7.0 SP1) from 80 "old" logins that I'll delete later.
I clearly need to maintain all the security options for the new logins.
Is there a way to do this, adding the logins with the new name and granting all security options? Is there a script that will do this task or can somebody help me in doing that?
Thank you.
Hi,
I have two servers (server1, server2). I was trying to access the data from server1 to server2. I linked the servers and set up the option for data access.
when I run the following command from server1: sp_remoteoption 'server2', 'sa', 'sa', TRUSTED, TRUE
It gives me the following error. How to handle this.
Server: Msg 15185, Level 16, State 1, Line 0
There is no remote user 'sa' mapped to local user 'sa' from the remote server 'server2'.
I appreciate your help.
Thank you.
Eric s.
I have a server that was recently upgraded from SQL 6.5 to 7.0 that contains almost 700 logins using standard security. Is there a limit to the number of logins that SQL will host? The logins/users have all been added using a GUI within an application and does not support NT authentication. Does this cause any known problems in SQL 7? When I right click on the database and go to properties / permissions MMC gets hung up. Also one of the systems analysts is convinced that SQL is dropping permissions (I disagree to this). Any one have any thoughts or experience with large numbers of logins / users?
Linda
Does anyone know of any 3rd party software applications that will make users change their SQL
logins every 3 months?
Thanks,
Heather
Hi!
How can I copy all logins from one SQL server 6.5 to
another one.
Thank you.
Anny.
How can I determine which users are using the database??
Thanks
I am new in sql and have problems about logins.
with sql 2005
1)I create a windows login in my home pc (win xp home sp2) and i can connect server with ssms this new user
but in my office (win xp pro sp2) create a windows login (it is a windows user also) and when try to connect with ssms take error 18456 (user name and pass is true!!!)
2)in my office pc i create a sql login which has no role. but this user can create users and dbs. but in my home pc, user has no role cant do thats...:eek:
Hi everyone,I am new to this forum.I hope some body will help me in this,I am trying this for the past 6hrs.
on my server there are some hundreds of sql logins are there,I need to identify the logins which doesn't associated with either any databases or dbroles,server roles.Later i need to delete those logins.Can any body help me in this.
Thanks in advance.
y r u doing like dis guys?
View 4 Replies View RelatedHi All,
I want to generate script for users and their associated permissions on the xyz database and at the same time I want to generate script for associated logins and their roles over the xyz database.
I am using SQL 2005 environment.
Needs urgent help.
Regards,
Austin
2 issues
i login using windows authentication. looks like i cant create a new sql server authentication login. what can i do?
and when i try to login with my sa login it pops the errror 18452. does this mean i didnt install mixed mode? i cant remeber.
After installing 2005 I have these 5 logins. Do I need them or should i wipe them out? What are they?
YPGA-SQL2005-DEVSQLServer2005MSFTEUser$IPG-SQL2005-DEV$MSSQLSERVER
YPGA-SQL2005-DEVSQLServer2005MSSQLUser$IPG-SQL2005-DEV$MSSQLSERVER
YPGA-SQL2005-DEVSQLServer2005SQLAgentUser$IPG-SQL2005-DEV$MSSQLSERVER
BUILTINAdministrators
NT AUTHORITYSYSTEM
=============================
http://www.sqlserverstudy.com
We created web site which has NT Authentication....
Using service account
SERVICE ACCOUNT
WEBGROUP
This has WEBGROUP_USERS
WEBGROUP_USERS
Tracey
John
When myself logs in...
Connect To SQL i see
in sysprocesses WEBGROUP
Is there a way to pass TRACEY to the process rather than having
SQL Login set up..
Reason is if someone adds themselves to this group in AD they could have access to our applications im trying to figure out
how to not make AD drive the access to SQL.
Thanks
Hello,I'm currently developing an access application which uses sql server as abackend.What i'm uncertain about, is how i should setup security without creatingtoo many sql server logins. I'm convinced that i should limit access to sqlserver, otherwise people can get in with other means than my application(e.g. odbc, sem, etc.).I've setup sql server in mixed mode and created a login for my applicationto connect to a database.So, what is the best way to implement tight security, but still be able tolog the current user and hostname? If a separate table is needed to createdusers and passwords, please tell me how to use it, i've never done thatapproach.Thnx------------------------------------------------------This mailbox protected from junk email by MailFrontier Desktopfrom MailFrontier, Inc. http://info.mailfrontier.com
View 1 Replies View Related