As from what i see there are different system files for logins.
Is there a way to transfer SQL 2000 logins to SQL 2005 i am used to the
sp_help_revlogin i think thats the script..in 2000
Is there a way to convert all logins from SQL 2000 to SQL 2005
I backup SQL 2000 DB and Restored to SQL 2005
Under the Database Name, Users when i click properties the login name is blank.
When i restored from SQL 2000 to another SQL 2000 Server i would run an orphan fix
declare @usrname varchar(100), @command varchar(100)
declare Crs insensitive cursor for
select name as UserName from sys.sysusers
where issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
order by name
for read only
open Crs
fetch next from Crs into @usrname
while @@fetch_status=0
begin
select @command=' sp_change_users_login ''auto_fix'', '''+@usrname+''' '
exec(@command)
fetch next from Crs into @usrname
end
close Crs
deallocate Crs
I changed the sys.sysusers from sysusers.
But when i run this it does not work.
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207
An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.
Does anyone know how to fix the logins for all of the users rather than one at a time for SQL 2005
Thanks
Hi
How to transfer logins from 2000 to 2005?
Raj.
Hey. 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
Hi All,
I have just migrated couple of test databases
from SQL Server 2000 to 2005 (side by side).
I also migrated the server level logins using SSIS transfer logins task, available on SSIS 'Transfer logins', I selected all the databases that I have migrated so that I have all database users account in server logins (to avoid orphaned users); but I don't have all the database users in server logins, also the sys.sysusers doesn't have the database users, that I have moved to SQL 2005. Can anybody help?
Also do I need to back up the SQL Server 2000 database and restore it on SQL 2005? What impact this operation can have?
Thanks for your time..
Bidyut
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...
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
I have a SQL2000 db that uses NT authentication for the users. All users have access to the public group on the db's and are assigned security inside the application. When I restore the db's to another server (can't do master) the logins seem out of synch. Users who had access to db's no longer did. I realize each user has their own sid. 2 questions - 1 if I dropped all the users and used DTS to import the logins would that be better. 2nd - I thought (couldn't find in BOL there was a sp_fixlogin% sp that could be run that might help me with this.
Thanks
Somebody knowns how to check the days and hours of the logins of an SQL Server user?
Thanks for the help
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 RelatedI was checking the logs because of some failing backups and found literally hundreds of enties for failed logins for user sa. This is does not seem to be related to the failed backups, because even after fixing the problem (disk space), the entries continue to pile up.
Needless to say, the information provided in the log is a bit sparse: date and time, source (Login), message (Login failed for user 'sa'.). If that is actually enough to go on, then there must be some way of cross-referencing this information with that found somewhere else. Is there a 'somewhere else' or are there other things I need to do in order to track this down?
I don't suspect any malicious intent, but I'm not ruling it out. My main concern at this point is twofold: to gain a better understanding of system management; to identify and correct the failed login.
Any recommendations, whether tools, documentation, or otherwise are very much appreciated.
Ron.
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.
What is the relationship between the users in a database and the logins on the server.
View 1 Replies View RelatedHi all,
I am using SQL 2005 (Enterprise Edition, SP2) Mirroring with a Principal, Mirror and Witness set to High Safety mode and am a little confused!
As far as I understand it, during a failover, although the database content will automatically be available on the Mirror any user accounts will need to be either manually created or manually mapped to the new principal. Is this the case? If so, I can't really see the purpose of using Mirroring (in my scenario anyway).
For example - We principally use the database servers for our web applications which we connect to using ADO.NET. Each database and Web app has a different SQL user account to allow access etc. If we experience a failover this means the users will not be able to use the web app until we have manually recreated/mapped the SQL accounts for each and every database. Surely this is a massive oversight?! Or am I simply getting the wrong end of the stick.
Any help/guidance would be greatly appreciated!
Thanks,
Richard
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 RelatedI was wondering if anyone could help me with this. I am currently working on a project for reporting on a new acquisition the company have made. Unfortunately, the people I have to give access to the SQL Server are using Netware for authentication.
Is there any way to allow these people to log into SQL Server without having to create a SQL authenticated user for each of them?
Thanks
Hi, all.We have a couple of pathological sql servers that have lots and lots ofpage faults per second, up to 4000. Our client programs are written inC#/.NET 1.1 and utilizes connection pooling.Some of the client programs seems to log in hundred of times persecond, as reported by perfmon->.SQLServer:GeneralStatistics->Logins/sec. Stopping the client programs reduces thatnumber significantly.We've done code reviews of the client programs and they look OK.Monitoring .NET connections&pools does not show anything suspicicous.We're currently rewriting the clients to use one db connection insteadof the pools, but that takes some time and may introduce bugs. Doesanyone know why we have these problems and/or why logins/sec is sohigh? I'm thinking "bugs in the .NET client", but really have noidea...One thought I had was that the Page Faults reported for sqlsrv.exe isrelated to memory mapped IO and therefore can be ignored. Right orwrong?Any thoughs/pointers/ideas, even wild guesses, are most welcome.BjørnPS: The server memory is fixed at 1.5GB out of 2GB physical ram,clients run on the same machine and use TCP/IP comm.(I know...) Thehost itself is not paging.
View 4 Replies View RelatedHello,
We use one standard account with a password that is never given out.
In SQL 2000 we had a script to extract the password in encrypted format so that it could be run as part of the installation process.
That same process does not work for SQL 2005.
Below is a sample of the script that we were deploying for SQL 2000 installations.
-------------------------------------------------------------------------------------------------------------
-- Login: CPAPP
declare @pwd varchar(50);
SET @pwd = CONVERT (varbinary(256), 0x01003402EC1BDADF45C9D788C23459BC36D73E5B2B9F2F235138F6BB8D0CD2317FCBA41EB59D191801AC287A14FF) EXEC master..sp_addlogin 'CPAPP', @pwd, @defdb = 'CPSQL' , @sid = 0xE5994FEF661AF842A0CA38AAFEB4360F, @encryptopt = 'skip_encryption'
SET @pwd = CONVERT (varbinary(256), 0x01003402EC1B9DA21489EC2D47CA9B8549255E9C9ACD4260661DFE04E9A0AFA631A27676C7A79E2DE20A52265666) EXEC master..sp_addlogin 'CPREPORT', @pwd,@defdb = 'CPSQL' , @sid = 0xD7E65E9AE7E87F4F9C72929A0B37F35C, @encryptopt = 'skip_encryption'
SET @pwd = CONVERT (varbinary(256), 0x0100A44D0526FABFF90D4CA524FA6415DD998256EB62963309D5F561AC5B116318E4F93D2159D9BAC95F356EBDC2) EXEC master..sp_addlogin 'cpdata', @pwd, @defdb = 'CPSQL' , @sid = 0xBE1AC61A2A8D8543ACAF2D403AAD96A4, @encryptopt = 'skip_encryption'
-------------------------------------------------------------------------------------------------------------
The procedure that we are using to script the logins is from the Microsoft support page:
http://support.microsoft.com/default.aspx?scid=kb;en-us;246133
Is this what we should be using for SQL 2005?
Please advise.
Thanks in advance
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.
I do not understand the sequence in which to add logins/users. A coupleof things to note. I can not use vbscript - it has to be done using thesqldmo objects (or in a sql script).The sequence I use now is:1- create login, set default db2- add login to sql server3- set db owner4- create user5- add user to master db6- add user to tempdb - blows up hereError 'Microsoft SQL-DMO: [SQL-DMO]This object is already in acollection.'I need the user in 3 dbs. Any help greatly appreciated. Thinking aboutit...I bet by adding it to the master db that it is automatically addedto tempdb so I will try to add it to the 2rd db and see if that works.Thanks!-Will
View 2 Replies View RelatedRight im getting slightly annoyed LOL
We have an MSSQL 2005 box in the shared hosting environment now the amount of attacks we receive every hour is a crazy amount. The usual stuff as below
Login failed for user 'sa'. [CLIENT: ]
Now i can simply block the ip's in ipsec but the amount of attacks is crazy and its taking all my time to keep track of them all, i know its just websites, automated tools etc, mssql is set to lock accounts after 4 invalid logins, well to make security sense i renamed the sa account as its default on most servers.
What i want to know is after so many attacks why can't mssql implement ipsec security on its own and block the client ip, come on after 20 attempts you know the IP is brute forcing the accoutn in question.
Any advice people?
Its a high performance server but it doesnt need brute force attacks against it all day long, LOL.
what is maximum no. of concurrent Logins Possible on a SQL server 2005 Database?
View 3 Replies View RelatedHi
We would like to install Sql 2005 Enterprise Edition (including database engine, reporting service, integration service and analysis service) as a sepearte instance on a server which already has Sql 2000 with reporting services and analysis services. We do not want to disturb the existing sql 2000 setup.
If we do that then what will happen to my earlier sql 2000 reporting service? Will it be upgraded to sql 2005 reporting service? I heard that reporting services are instance unaware application. Where will be the default reporting service database available?
Please help us.
Regards,
Sankar N
I am attempting to move some SQL 2000 databases to SQL 2005. My main production database does not seem to want to move. When I use the SQL 2005 GUI the .bak backup file is marked 'Incomplete'. When I attempt to restore the backup file I get a 'RESTORE detected an error on page (0:0) in database' message. I saw a thread in the SQL Express forum suggesting trying to restore from the T-SQL level to get the GUI out of the picture and I get the same 'error on page (0:0)' message. However when I take the same file and use SQL 2000 Enterprise Manager it restores with no problems.
Any ideas?
Thanks
Mike Mattix
Hi, I am trying to edit some data from a SQL2000-datasource in ASP.NET 2.0 and have a problem with a column that has bit-data and is used for selection. SQL2005 works fine when declaring <SelectParameters> <asp:Parameter DefaultValue="TRUE" Name="APL" Type="boolean" /> </SelectParameters>When running this code with SQL2000, there are no error-msgs, but after editing a record the "APL"-column looses its value of 1 and is set to 0. Looks like an issue with type-conversion, we've hit incompatibilities between SQL200 and 2005 with bit/boolean several times before. So, how is this done correctly with SQL2000? (I've tried setting the Type to "int16" -> err. Also setting Defval="1" gave an err) ThanksMichael
View 2 Replies View RelatedHi,i have SQL 2000 and 2005 on same machine(with different intance names,of course), my laptop - XP with SP2. The 2005 works fine but i can'tconnect on SQL 2000. All the the SQL services are started.Any idea? Have i to reinstall 2000?Tks,Lourival
View 1 Replies View RelatedI have to merge the data from two databases, one is in SQL Server 2005 format, one is in 2000. The merged data will then reside on a SQL Server 2000 platform. Is there an easy way to do this through Management Studio or Enterprise Manager? Or will we have to export the data from the 2005 database to a flat file and import it into a new 2000 database. And then do the merge?
TIA
I am in the process of migrating from Sql Server 2000 to 2005. Part of my plan is to move some database's to 2005, but use the 2000 compatibility mode for the short term. My issue is this, our DR boxes are still on SQL Server 2000, would I still be able to use our log shipping processes? Or would I be better off in starting with migrating the DR boxes to 2005 first?
Thanks in advance.
I have several SQL 2000 servers I need to setup transactional (non updatable) replication with. The structure is:
SQL Server 2000 as Publisher/Distributor
SQL Server 2005 Standard as Subscriber
The connection is via the Internet with snapshots using FTP.
I setup the first set (2 databases at location A). They work wonderfully. I created the publication and then subscribed using MGMT Studio for 2K5.
II am setting up the same scenario for location B. Here is my problem:
In MGMT Studio I connect to the publisher (SANDRA). I right-click a publication and choose New Subscriptions..., the publication is already selected. I click next - Run each agent at its Subscriber is selected and the only option (this is desired), I click Next
HERE IS THE PROBLEM:
On the Subscriber's screen there are no Subscribers listed. When setting up location A the subscribing server was listed and I could choose a database. The Next button is greyed out and there is no way to create/add one.
I tried setting up the subscription by right-clicking the subcribing server's Replication folder in MGMT Studio but I get the same result (except that I have to authenticate with the publishing server which works fine).
WHAT'S DIFFERENT:
Location A is SQL Server Standard (SP3) running on SBS2K3. It is obviously on a domain and so SQL Server and the SQL Agent are running under domain accounts. Location B is a Windows XP SP2 machine running SQL Server Personal Edition (it actually says Development Edition in the properties window).
The databases are the same strucutre, different data. At location A the firewall is set to allow 1433->*any* and *any*->1433 where *any* is 1024 or higher. On the XP machine the firewall is set to allow port 1433. I don't think this is the issue because I've turned the firewall off on the XP machine and I get the same result.
ANY IDEAS?
Most of our sql servers ar still sql 2000. Our programmers created many sql 2000 database diagrams using EM. But they can not access them under sql 2005. (They now have only sql 2005 tools installed on their boxes.)
Question: can we reinstall the sql 2000 client tools on their boxes without affecting the current sql 2005 install on their boxes?
Question: is there any workaround negating the need to ihstall the sql 2000 client?
TIA,
barkingdog
Hello Everyone,
I'm trying to connect to Desktop SQL Server 2000 from Windows mobile PC Emulator (VS 2005). I need a direct connection using connection string to SQL Server 2000 through local wireless network without IIS.
Bellow is the code that I use. After executing this code I get an error on line Conn.Open(). Error says SQL Server does not exist or access denied.
SQL is un and running, and I can log in using SA username from the desktop. Even if I chance IP for another SQL server in my connection string I still get the same error. There is no firewall of any kind running.
Dim connectionSTR As String = "Persist Security Info=False;Integrated Security=False;Server=192.168.0.202,1433;initial catalog=MyDB;user id=sa;password=;"
Dim Conn As SqlConnection
Conn = New SqlConnection(connectionSTR)
Conn.Open()
If Conn.State = ConnectionState.Open Then
MessageBox.Show("Open")
End If
About my environment: SQL Server 2000 is running on Desktop PC with Windows XP SP2. Application which I need to connect to SQL Server is in Visual Studio 2005. I execute the application in Windows Mobile PC Emulator and try to connect to SQL Server from emulator.
Your advice and help is very appreciated
Thank you
Ika
Hello,
I would like to ask you if there is better to recreate database structure on 2005 from 2000 and move data or to just load
2000 backup.
Currently I loaded the backup, but I am wondering if there might be slightly better performance on 2005 when recreating structure on 2005 to loading 2000 backup?
Does loading 2000 backup create 2005 binary structure?
Thanks for help in advance