Copying Logins
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
ADVERTISEMENT
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
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
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
Apr 12, 2004
How do you make Asp.net use something other than the local ASPNET user for data access in sql server
View 3 Replies
View Related
Jul 10, 2001
I 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.
View 2 Replies
View Related
Dec 21, 2000
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.
View 1 Replies
View Related
Apr 13, 2000
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
View 3 Replies
View Related
Jun 19, 2000
Does anyone know of any 3rd party software applications that will make users change their SQL
logins every 3 months?
Thanks,
Heather
View 1 Replies
View Related
Mar 14, 2000
Hi!
How can I copy all logins from one SQL server 6.5 to
another one.
Thank you.
Anny.
View 1 Replies
View Related
Sep 30, 2004
How can I determine which users are using the database??
Thanks
View 2 Replies
View Related
Feb 28, 2008
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:
View 1 Replies
View Related
May 15, 2008
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.
View 13 Replies
View Related
May 22, 2008
y r u doing like dis guys?
View 4 Replies
View Related
Jun 12, 2008
Hi 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
View 2 Replies
View Related
Apr 19, 2007
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.
View 6 Replies
View Related
Jun 13, 2007
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
View 2 Replies
View Related
Aug 7, 2007
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
View 2 Replies
View Related
Jul 23, 2005
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
Jul 20, 2005
On a huge Sql-Server 7 installation, we have various client applicationsdistributed along the Lan, accessing one main database.Each application accesses the db using one out of around ten logins. Most ofthem, have only DBDataReader right on the db, as they are consultationconsolles only.In order to monitor db usage, the customer requires some kind of log of useraccess.My need, mainly, is to INSERT a record into a log table, recording Date,Time, Login, Host of each access.But, and this is the problem, the job has to be done by the server itself,not by each client, because of various reasons:1) we don't like to increase rights of logins2) we don't plan to change anything in our custom client application3) few of those client applications have been developed by foreignsuppliers, so we cannot change them.My question is: does it exist any kind of authentication LOG, which I canwork on?Or, is it possible to activate a kind of TRIGGER, reacting on loginauthentication?Thanks in advanceAlberto
View 2 Replies
View Related
Jul 20, 2005
How can I get a list of all SQL server logins that are on a given sqlserver? What sql query would get me that? Or is there an sp thatgives me that. Also, if it could list what db they have rights tothat would be great also.
View 2 Replies
View Related
Jul 20, 2005
I am confused about windows account, win dimain account, sql loginaccoun and sql database user account.... can you give me a freshexample to show the exact relationship between them and how they worktogether?
View 1 Replies
View Related
Dec 26, 2007
hi ,
we have nearly 1 thousand logins are there. out of thousand only 8 persons are directly interacting with sql server .
remaining are accessing the databases through application only. my query is every one is having sysadmin permissions .
if i remove the permissions to them, they can't able access the databases.i am new to the organization.perviously no one was there to maintain the server .i don't know how to resolve this problem.my manager asked me to do something for this one.
can anyone help me to reslove this prb
regards,
manoj
View 4 Replies
View Related
Jul 25, 2006
How can I write a script to generate the list of SQL users that have sysadmin privileges. I would also like to do the same for the windows accounts as well.
Please let me know.
Thanks.
View 4 Replies
View Related
Jun 19, 2007
I'm monitoring 2005 while running our application by looking at
sys.dm_os_performance_counters, general statistics, logins/sec.
I began testing a .NET 2 application, and logins/sec went from 63 to 11,433 in 30 minutes. Even though I have closed the application, it is still showing 11,449 - the only thing that's happened in the last hour is that I've run some queries from QA. is this login number cumulative, or is it an actual reflection of logins/sec? How on earth could that many logins be happening when no one else can even get to that server?
View 7 Replies
View Related
Jan 19, 2007
Hi, I'm relatively new to the admin side of websites and DBs. In the past, with hosted environments I've been used to just using SQL logins to connect to the DB from a web app, but for something I'm delevoping at the moment I have control over the DB and web server and was wondering if using a trusted connection would be better. After reading around it seems that Microsoft warns against using mixed mode authentication because of clear text passwords, the DB I'm working again currently isn't in mixed mode. So far I have set up the application to run in its own application pool under a limited user account that also has limited access to the DB. Though like this every new application that needs access to a different DB would require a different pool. What I'm asking essentially is what's the best way of going about things; set up a new pool and user for each app that requires it, using a trusted connection or turn on mixed mode authentication on on the DB. Or a mixture of the two?
View 5 Replies
View Related
Jul 9, 2001
I have created 2 SQL logins for use in connecting to an SQL server database (v7) from my web server. My idea is that one login (WebAdmin) would be assigned the db_owner role and one login (WebPublic) db_datareader and db_datawriter roles.
However, I have run into a problem. Only one of the logins works. The other generates an "Invalid object name" error. The login that works is the login that is listed as the owner in the SQL Server Enterpise Manager Tables pane. Even if the other login has a db_owner role in the user properties box.
Can anyone tell me what is going on?
My code looks something like this:
<%
Set CN=Server.CreateObject("ADODB.Connection")
CN.Open "Driver={SQL Server};SERVER=<ip address>;UID=<either WebAdmin or WebPublic>;PWD=<appropriate password>
Response.Write "Connection Opened!<BR>"
[this part works in both cases]
Set RS=Server.CreateObject("ADODB.Recordset")
RS.Open "Select * from map_gallery_primary_geographies", CN,3, 3
[this generates the "Invalid object name" error for the login that is not listed as owner in the Enterprise Manager]
Response.Write "Value of the first field's record: " & RS(0)
View 2 Replies
View Related
Jul 12, 2000
HI,
If i transfer logins from one server to another server using dts, I am getting all the logins and permissions... How will i get passwords too..
-thank u.
--Siva
View 2 Replies
View Related
Sep 18, 2000
Does anyone have a sample SQL DMO script to transfer SQL Server logins from one server to another including the passwords ? any pointers will be greatly appreciated
View 1 Replies
View Related
Feb 18, 2000
Id like to quick method to record login connections and discconections,
any ideas??
Many thanks.
View 1 Replies
View Related
Aug 3, 2000
The vendor originally set the db up with user logins. I am migrating the db and need to know how to migrate all the logins as well.
View 2 Replies
View Related
Jan 18, 2001
Does anyone have any ideas how I can track when someone logged in and out of SQL Server and compile that information over a 3 month period?
View 1 Replies
View Related
