Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1253
  • Last Modified:

MSDE MSSQL Authentication

I am running XP Pro.
I have installed MSDE from the Office XP CD, this includes SQL Server for development, but not the full version.
I have setup user and system DSN's in ODBC for SQL Server, and tested the connections in ODBC, which are successful.
Trying to connect from an asp page (IIS 5.1) using either connection string or dsn, I get a logon failed message from SQL. Using either the 'Windows NT Authentication' or 'SQL Server Authentication', the problem is the same.
My question is:
How (where) can I specify login name? Is there a default login for SQL Server?

here is my connection string:
strSQLServer = "localserver"
strDatabase = "c:\MSSQL\DATA\mydata.mdf"
strUserID = "windowsuser"
strPassword = "windowspassword"

strConn = "Provider=SQLOLEDB;" & _
          "Data Source=" & strSQLServer & ";" & _
          "Initial Catalog=" & strDatabase & ";" & _
          "User ID=" & strUserID & ";" & _
          "Password=" & strPassword & ";" & _
          "Persist Security Info=False;"


However, I am fairly sure that the connection string itself is fine,
I just need the correct username/password, or a way to set SQL Server to accept a username/password combination.
Thanks,
-h
0
hanneman
Asked:
hanneman
  • 40
  • 30
  • 19
  • +4
1 Solution
 
adatheladCommented:
> strDatabase = "c:\MSSQL\DATA\mydata.mdf"

When you try to connect to an SQL database, unlike MS Access, you don't specify a database file. You must specify the actual name of the database.

e.g.
strDatabase = "YourDatabaseName"

Cheers
0
 
hannemanAuthor Commented:
oops. was typing in example values, .mdf isnt there in my actual connection string.

here is the error message:
Microsoft OLE DB Provider for SQL Server (0x80040E4D)
Login failed for user 'Admin'.


0
 
Eugene ZCommented:
"c:\MSSQL\DATA\mydata.mdf" is database data file

must be -> Initial Catalog=<databasename>;
e.g. 'Initial Catalog=PUBS'
(not c:\MSSQL\DATA\pubs_data.mdf"
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
adatheladCommented:
Ok...sorry to confirm again, but from what you've said it sounds like you are passing in:
strDatabase = "c:\MSSQL\DATA\mydata"
when there should be no path info in there. I like to clear any confusion!

0
 
adatheladCommented:
Try using an SQL server account.
By default, try account "sa", password ""

strSQLServer = "localserver"
strDatabase = "databasename"
strUserID = "sa"
strPassword = ""

strConn = "Provider=SQLOLEDB;" & _
         "Data Source=" & strSQLServer & ";" & _
         "Initial Catalog=" & strDatabase & ";" & _
         "User ID=" & strUserID & ";" & _
         "Password=" & strPassword & ";" & _
         "Persist Security Info=False;"
0
 
hannemanAuthor Commented:
ok, I have taken out the path data, and database filename extension thanks!
but I still get the login failed error.

strSQLServer = "localserver"
strDatabase = "mydata"
strUserID = "windowsuser"
strPassword = "windowspassword"

strConn = "Provider=SQLOLEDB;" & _
         "Data Source=" & strSQLServer & ";" & _
         "Initial Catalog=" & strDatabase & ";" & _
         "User ID=" & strUserID & ";" & _
         "Password=" & strPassword & ";" & _
         "Persist Security Info=False;"
0
 
adatheladCommented:
ok, now try my previous post (use SQL Server account="sa" password=""). See if that works.
0
 
hannemanAuthor Commented:
ok, SQL Server lets me use 'sa' with a blank password in the ODBC, (now set up to use 'SQL Authentication'). However, I am still getting a login error:

Microsoft OLE DB Provider for SQL Server (0x80004005)
Cannot open database requested in login 'mydata'. Login fails.
0
 
hannemanAuthor Commented:
connection string:

strSQLServer = "localserver"
strDatabase = "mydata"
strUserID = "sa"
strPassword = ""

strConn = "Provider=SQLOLEDB;" & _
          "Data Source=" & strSQLServer & ";" & _
          "User ID=" & strUserID & ";" & _
          "Password=" & strPassword & ";" & _
          "Initial Catalog=" & strDatabase & ";" & _
          "Persist Security Info=False;"
0
 
Eugene ZCommented:
are you sure 'sa' pwd is blank.
do you have EM? - can you register your MSDE and see what login is active or change sa's password or any UID....
0
 
hannemanAuthor Commented:
I think the problem is now in the ODBC setup, if I use one of the databases that comes with SQL server, then it works.

e.g.

strSQLServer = "localserver"
strDatabase = "master"
strUserID = "sa"
strPassword = ""

strConn = "Provider=SQLOLEDB;" & _
         "Data Source=" & strSQLServer & ";" & _
         "User ID=" & strUserID & ";" & _
         "Password=" & strPassword & ";" & _
         "Initial Catalog=" & strDatabase & ";" & _
         "Persist Security Info=False;"

but when I try to 'attach database filename' using: 'mydata', I get error: 'the database entered is not valid'  
0
 
arbertCommented:
You cannot use Windows Authentication to connect to MSDE from IIS unless MSDE is running on the same box as IIS--is this how you're configured or is MSDE on a different box?

If you have Enterprise manager, do you actually see a database called MyData?


Brett
0
 
hannemanAuthor Commented:
MSDE and IIS are running on the same box.
I dont have EM, I am told there is a free download of it on msdn, but i havent found it yet.
0
 
Eugene ZCommented:
use 'osql' from DOS
0
 
hannemanAuthor Commented:
osql:
runs, prompts for password, wont accept my password, or blank password.
0
 
Eugene ZCommented:
or free "MSDE Query" GUI from http://www.msde.biz/
0
 
Eugene ZCommented:
osql:
what about Trusted connection '-E' if you have Windows and SQL authentication..
0
 
hannemanAuthor Commented:
I have downloaded MDSE Query twice, both times i get error:
'application failed to initialise'
0
 
hannemanAuthor Commented:
osql:
'-E' login fails.
0
 
Eugene ZCommented:
be sure you are in admin group on this box before '-E'

MSDE Query:
What is required to run MSDE Query?

The free Microsoft runtime .NET framework is required.

http://msdn.microsoft.com/library/default.asp?url=/downloads/list/netdevframework.asp
0
 
adatheladCommented:
Are you sure, you actually have a database called "mydata" on your SQL Server?
0
 
hannemanAuthor Commented:
osql -E now goes. what should I do from there?

I have MSDE, with the MSSQL Server Running.
I have no idea how put put the database on the server, it is currently in the folder with the default databases.

0
 
adatheladCommented:
That is the reason why you can't connect to it and can connect to the system databases. If you have the .MDF and .LDF files try connecting to the "master" database as  you've been able to do, and execute the SQL statement given in the example below.
Just make sure you check the paths i've given below and make sure they match up with your .MDF and .LDF files.

Connection.Open <Your connection string to master database>

Dim strSQL
strSQL = "EXEC sp_attach_db @dbname = N'mydata',
   @filename1 = N'c:\MSSQL\DATA\mydata.mdf',
   @filename2 = N'c:\MSSQL\DATA\mydata_log.ldf'"

Connection.Execute strSQL

Once this is done, try connecting to database "mydata".
0
 
Eugene ZCommented:
run
osql -SyourSERVER -E -Q"master..sp_helpdb"
To see if "mydata" db on your MSDE
0
 
ispalenyCommented:
1.
osql -SServerName -Usa -dmaster
or
osql -SServerName -E -dmaster

Replace ServerName and type password on prompt.

Now, if you are connected write folowing query and press <Enter>
use mydata

Any errors occured?


0
 
ispalenyCommented:
Run REGEDIT and look at node
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer

LoginMode REG_DWORD 0x00000002(2) = mixed mode auth

Good luck!

0
 
hannemanAuthor Commented:
adathelad:
I dont have the .ldf file.
Tried to run the query (minus the '@filename2='), but got:
ADODB.Connection (0x800A0E78)
Operation is not allowed when the object is closed.

I have stopped and restarted, but same again.


ispaleny:
osql -SServerName -E -dmaster
runs, then I get a prompt:
1>
so i typed in:
1>EXEC sp_attach_db @dbname = N'mydata',
  @filename1 = N'c:\MSSQL\DATA\mydata.mdf'

but it just goes to the second line:
2>


I dont think it is the permissions now, I think adathelad got me past that with 'sa', but now I am stuck with how to put the database onto the server.
I will up points....

Thanks everyone so far!
-h
0
 
adatheladCommented:
Ok, if you can no .LDF file you need:

Dim oConnection
Set oConnection = Server.CreateObject("ADODB.COnnection")
oConnection.open <Your connection string to master database>

Dim strSQL
strSQL = "EXEC sp_attach_single_file_db @dbname = 'mydata',
   @physname = 'c:\MSSQL\DATA\mydata.mdf'"

oConnection.Execute strSQL
0
 
hannemanAuthor Commented:
we are getting there, but I think there is a problem with the .mdf file itself.

I ran:
 "EXEC sp_attach_single_file_db @dbname = 'mydata',
  @physname = 'c:\MSSQL\DATA\mydata.mdf'"

heres the new error:

Microsoft OLE DB Provider for SQL Server (0x80040E14)
The header for file 'c:\MSSQL\DATA\mydata.mdf' is not a valid database file header. The PageAudit property is incorrect.
0
 
hannemanAuthor Commented:
The .mdf is a file that I requested from my webhost. I was sick of developing on Access and deploying on SQL. Its not a service they usually provide and im loath to ask for another file. the only other file I can get my hands on is the mydata.bak. dont suppose thats any use?

more points adathelad? :-p
0
 
adatheladCommented:

Ok, try this as the SQL statement to execute:

strSQL = "CREATE DATABASE mydata
ON PRIMARY (FILENAME = 'c:\MSSQL\DATA\mydata.mdf')
FOR ATTACH"

0
 
adatheladCommented:
If that doesn't work, we can try restoring the "mydata.bak" file:

strSQL = "RESTORE DATABASE mydata
   FROM DISK = 'c:\MSSQL\DATA\mydata.bak'"

I've assumed the .bak file is in the same location.
Let me know how it goes.
0
 
ispalenyCommented:
Try
exec sp_attach_single_file_db @dbname='mydata',@physname='c:\MSSQL\DATA\mydata.mdf'

0
 
adatheladCommented:
ispaleny: we tried that and it returned an error. See 4-5 posts back :)
0
 
ispalenyCommented:
It can be a version conflict. You can upgrade database file, but you cannot downgrade it.
Run: SELECT @@VERSION
If it returns a value under 8.00.760, download and run a service pack (SP3).
0
 
hannemanAuthor Commented:
how it went:

strSQL = "CREATE DATABASE mydata
ON PRIMARY (FILENAME = 'c:\MSSQL\DATA\mydata.mdf')
FOR ATTACH"

result:
The header for file 'c:\MSSQL7\DATA\mydata.mdf' is not a valid database file header. The PageAudit property is incorrect.

strSQL = "RESTORE DATABASE mydata FROM DISK = 'c:\MSSQL7\DATA\mydata.bak'

result:
Timeout Expired.


Now that I think about it, being able to use the restore option would be the best, as I can easily download the daily .bak of the database....
so close! btw, its a 10Mb DB.
0
 
adatheladCommented:
once you've opened the connection, try setting the ConnectionTimeout property as below:

oConnection.ConnectionTimeout = 60

This will mean it will only time out after 60 seconds, as opposed to the default 15. See if that helps. It shouldn't take too long though.
0
 
ispalenyCommented:
Query SELECT SERVERPROPERTY('PRODUCTLEVEL') should return "SP3".
0
 
hannemanAuthor Commented:
hmmm.

version on my machine:
Microsoft SQL Server 7.00 - 7.00.623 (Intel X86) Nov 27 1998 22:20:07 Copyright (c) 1988-1998 Microsoft Corporation MSDE on Windows NT 5.1 (Build 2600: )


version of online SQL Server:
Microsoft SQL Server 2000 - 8.00.665 (Intel X86) Jul 29 2002 15:02:32 Copyright (c) 1988-2000 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 3)    


56MB. hmmm. I hate 56k sometimes... but its free...
0
 
ispalenyCommented:
It is not MSSQL 2000!!!!!!

See 7.00.623
0
 
ispalenyCommented:
You have an incorrect MSDE installed!!!!!!!!!!!
0
 
adatheladCommented:
yes, that could definately be a problem! :D

hannemane - you'd need SQL 2000 on your machine
0
 
ispalenyCommented:
0
 
hannemanAuthor Commented:
will try again when download complete.
thanks everyone! I'll let you know how it goes...
0
 
adatheladCommented:
good luck with the download :)
I once downloaded a 89MB file via my 56k modem and so I appreciate how painstaking it is!!
0
 
arbertCommented:
That link is only Service Pack 3 for EXISTING MSDE 2000 installations.....It won't upgrade your 7.0 installation....

Brett
0
 
ispalenyCommented:
SQL2kdesksp3.exe includes all of the files that are required to install a new instance of Desktop Engine (.msi files), to upgrade all existing instances of Desktop Engine (.msp files), as well as to consume merge modules (.msm files) into applications.
0
 
hannemanAuthor Commented:
which link? I dont want to be downloading for 3 hours only to get the wrong file....
0
 
hannemanAuthor Commented:
ok, will download the 70MB one. probly more like 4 hours.
0
 
ispalenyCommented:
0. See you after 4 hours (maybe)
1. Download
2. Unpack
3. Commandline:
 C:\sql2ksp3\MSDE\setup.exe /upgradesp SQLRUN INSTANCENAME=MSSQLSERVER BLANKSAPWD=1
0
 
QJohnsonCommented:
You can do an awful lot of poking around on your own with MSDE with the SQL-DMO library.  It gives you access to all the functionality available in Enterprise Manager.  In a clean MSDE install, all you really need is the defaul user name (sa) and the blank password to get the DMO running and it will provide you a list of "register servers" that will include your MSDE engine.  So you can get the Server name from there if you forget it.

Once you have a server name, you can very easily write code to inspect, create, and edit logins.  You can really do anything you could do with EM.  So you can build some great little utilities for your development environment.
0
 
hannemanAuthor Commented:
downloaded.
unpacked.
clicked on setup.exe, "Incorrect Instance Name"
uninstalled msde.
clicked on setup.exe, "Incorrect Instance Name"

run command line:
C:\sql2ksp3\MSDE\setup.exe SQLRUN INSTANCENAME=MSSQLSERVER BLANKSAPWD=1

"Incorrect Commandline Parameters"

run commandline:
setup.exe INSTANCENAME=MSSQLSERVER BLANKSAPWD=1

Installed, except, That I could not find any folders, nor could I find a way of starting the SQL Service Manager.

Could not see any way of uninstalling sp3.

Installed previous version of MSDE, errors during install:
-vswitch.

Now I dont seem to have either installed properly nor do I know how to uninstall either of them, as I cant find them in add/remove, nor canI find an uninstall file.

sorry people, I seem to have managed to mess this one up nicely....


0
 
ispalenyCommented:
Did you lose any data?
0
 
hannemanAuthor Commented:
umm, not sure what you mean. I still have all the original .mdf and .bak files (I was working with copies...) So the database files should be okay.

And I still have all the sp3 files.
0
 
ispalenyCommented:
Upgrade from MSDE 7.0 worked better than a simple MSDE 2K installation. Anyway, you want install MSDE 2K.

Problem can be fixed in registry.

1. Use regedit to delete all keys in
"HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server"
"HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer"

2. Run setup again.
0
 
hannemanAuthor Commented:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server
found.
delete contents of folder, or just delete all subfolders?

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer
not found

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServ80\MSSQLServer
found.
delete contents of folder, or just delete all subfolders?

points up!

0
 
ispalenyCommented:
These registry things are comming from THE HELL.
I have HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer everywhere, in every installation. But when I come to this forum ALL USERS have it elsewhere. One in Cars, another in MSSQLServ80.

Delete
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServ80
with all children, grandchildren and other relatives!
0
 
hannemanAuthor Commented:
deleted all keys.

ran:
setup.exe INSTANCENAME=MSSQLSERVER BLANKSAPWD=1

Installed, except, That I can not find any folders, nor can I find a way of starting the SQL Service Manager.

0
 
hannemanAuthor Commented:
c:\programfiles\microsoft sql server folder is there, but no msde
0
 
hannemanAuthor Commented:
how about I try delete all keys again, try and get back to where I was before (old version running) then perform the update...?
0
 
ispalenyCommented:
Can you run "c:\Program Files\Microsoft SQL Server\mssql\Binn\sqlservr.exe" ?
0
 
hannemanAuthor Commented:
double clicked it, got a dos pop up, with lots of good messages in it.

2003-03-19 10:20:00.82 server    Microsoft SQL Server  2000 - 8.00.760 (Intel X8
6)
        Dec 17 2002 14:22:05
        Copyright (c) 1988-2003 Microsoft Corporation
        Desktop Engine on Windows NT 5.1 (Build 2600: )

2003-03-19 10:20:00.82 server    Copyright (C) 1988-2002 Microsoft Corporation.

2003-03-19 10:20:00.82 server    All rights reserved.
2003-03-19 10:20:00.82 server    Server Process ID is 3432.
2003-03-19 10:20:00.83 server    Logging SQL Server messages in file 'C:\Program
 Files\Microsoft SQL Server\MSSQL\LOG\ERRORLOG'.
2003-03-19 10:20:00.84 server    SQL Server is starting at priority class 'norma
l'(1 CPU detected).
2003-03-19 10:20:00.87 server    SQL Server configured for thread mode processin
g.
2003-03-19 10:20:00.88 server    Using dynamic lock allocation. [500] Lock Block
s, [1000] Lock Owner Blocks.
2003-03-19 10:20:00.90 spid3     Starting up database 'master'.
2003-03-19 10:20:01.23 server    Using 'SSNETLIB.DLL' version '8.0.760'.
2003-03-19 10:20:01.26 spid5     Starting up database 'model'.
2003-03-19 10:20:01.64 server    SQL server listening on 172.180.7.189: 1433.
2003-03-19 10:20:01.65 server    SQL server listening on 127.0.0.1: 1433.
2003-03-19 10:20:01.67 server    SQL server listening on TCP, Shared Memory, Nam
ed Pipes.
2003-03-19 10:20:01.68 server    SQL Server is ready for client connections
2003-03-19 10:20:01.68 spid3     Server name is 'THEMIDNIGHT'.
2003-03-19 10:20:01.70 spid3     Skipping startup of clean database id 4
2003-03-19 10:20:02.07 spid5     Clearing tempdb database.
2003-03-19 10:20:03.00 spid5     Starting up database 'tempdb'.
2003-03-19 10:20:03.18 spid3     Recovery complete.
2003-03-19 10:20:03.19 spid3     SQL global counter collection task is created.

and now?
0
 
hannemanAuthor Commented:
BTW, sa login no longer works in odbc...
0
 
ispalenyCommented:
Run "c:\Program Files\Microsoft SQL Server\80\Tools\Binn\sqlmangr.exe"
0
 
ispalenyCommented:
?HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer80\MSSQLServer\LoginMode
0
 
adatheladCommented:
login "sa"
password ""

should still work. Have you tried connecting to the master database using these login details?
0
 
ispalenyCommented:
MSDE SP3 is by default in WinAuth.
0
 
hannemanAuthor Commented:
sqlmangr.exe runs, but the service will not start.

-----------

you told me:
"Delete
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServ80
with all children, grandchildren and other relatives!"

however
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\login mode = 1

---------------------

login "sa"
password ""

does not work. not through asp connection string or odbc:
"Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection."
0
 
ispalenyCommented:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\login mode = 1

1. Change to 2
2. Restart SQL Server
3. Login as SA
0
 
hannemanAuthor Commented:
1. Changed to 2
2. Restarted
3. Login successful

should I setup files and folders and run:
RESTORE DATABASE mydata FROM DISK = 'c:\MSSQL2000\DATA\mydata.bak ?
0
 
ispalenyCommented:
1. bak
CREATE DATABASE mydata
RESTORE DATABASE mydata FROM DISK = 'c:\MSSQL2000\DATA\mydata.bak
2. OR mdf
EXEC sp_attach_single_file_db @dbname = 'mydata',@physname = 'c:\MSSQL\DATA\mydata.mdf'"

0
 
ispalenyCommented:
Add apostrophe to RESTORE!!!
RESTORE DATABASE mydata FROM DISK='c:\MSSQL2000\DATA\mydata.bak'
0
 
adatheladCommented:
yes hanneman, try the methods i posted before that you tried last time....fingers crossed, touch wood....
0
 
hannemanAuthor Commented:
connect to master.
run:
strSQL = "CREATE DATABASE mydata"
oconn.execute(strSQL)

database created. :-)

run:
strSQL = "RESTORE DATABASE ...."
oconn.execute(strSQL)

error:
device activation... physical path

assume:
physical path from ISP is stored in the DB.

recreate physical path on local machine.

run.
DROP...
CREATE...
RESTORE

no errors, but:
some tables working, some get "invalid object"

0
 
ispalenyCommented:
Run query:

select u.name as OwnerName,o.name as TableName
from sysobjects o
left join sysusers u on o.uid=u.uid
where xtype='U'
order by u.name,o.name
0
 
hannemanAuthor Commented:
result:

owner, tablename

dbo,TblCountry
dbo,TblDiscipline
dbo,TblNotified
dbo,TblRegion
dbo,TblTransaction
mydata,tblanswers
mydata,TblAnswers2
mydata,TblConference
mydata,TblNotificationReq
mydata,tblsearch
mydata,TblUser

dbo owned tables are working....
table owned by the database are not...
0
 
hannemanAuthor Commented:
more points!!!
0
 
adatheladCommented:
try using the owner prefix in the select statement for the database owned ones.

e.g.
SELECT * FROM mydata.tblUser

0
 
ispalenyCommented:
Run
select * from mydata.tblanswers
0
 
hannemanAuthor Commented:
Anyone reccommend a good SQL Language syntax website?
or a book?
0
 
adatheladCommented:
0
 
hannemanAuthor Commented:
Using the owner prefix works, but I cannot change every SQL statement in my application...
how to change the owner?
0
 
ispalenyCommented:
A, Create user
1. exec sp_change_users_login @Action ='Auto_Fix',@UserNamePattern ='mydata'
2. Login as mydata (not sa)

B, OR Run
exec sp_changeobjectowner @objname='tblanswers', @newowner='dbo'
exec sp_changeobjectowner @objname='TblAnswers2', @newowner='dbo'
exec sp_changeobjectowner @objname='TblConference', @newowner='dbo'
exec sp_changeobjectowner @objname='TblNotificationReq', @newowner='dbo'
exec sp_changeobjectowner @objname='tblsearch', @newowner='dbo'
exec sp_changeobjectowner @objname='TblUser', @newowner='dbo'
exec sp_changeobjectowner @objname='object', @newowner='dbo'

0
 
hannemanAuthor Commented:
Using mydata:
A1:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Terminating this procedure. The Action 'Auto_Fix' is incompatible with the other parameter values ('mydata', '(null)').

B:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Object 'tblanswers' does not exist or is not a valid object for this operation.

Using Master:
A1:
Success.
A2:
Login to mydata failed for used mydata

so close...

0
 
adatheladCommented:
Try:

exec sp_changeobjectowner @objname='mydata.tblanswers', @newowner='dbo'
0
 
ispalenyCommented:
Once again, maybe better.

A, Create user
1. exec sp_addlogin 'mydata'
2. exec sp_change_users_login @Action ='Update_One',@UserNamePattern='mydata',@LoginName='mydata'
3. Login as mydata (not sa)

B, OR Run
exec sp_changeobjectowner @objname='mydata.tblanswers', @newowner='dbo'
exec sp_changeobjectowner @objname='mydata.TblAnswers2', @newowner='dbo'
exec sp_changeobjectowner @objname='mydata.TblConference', @newowner='dbo'
exec sp_changeobjectowner @objname='mydata.TblNotificationReq', @newowner='dbo'
exec sp_changeobjectowner @objname='mydata.tblsearch', @newowner='dbo'
exec sp_changeobjectowner @objname='mydata.TblUser', @newowner='dbo'
0
 
ispalenyCommented:
I think A is the better choice. It will work exactly the same way as on the ISP server.
0
 
hannemanAuthor Commented:
I chose B.
result: 100% working!!!

Firstly:
A great deal of thanks is owed to both adathelad and ispaleny! this is one long PAQ!

Secondly:
How do you think the points should be split?
I am thinking:

ispaleny: 200
adathelad: 150

would that be fair? adathelad already has 50,000!!!
0
 
hannemanAuthor Commented:
ok, I try A
0
 
hannemanAuthor Commented:
I changed the ownership of sometables back to mydata, and the created the user. Works fine. 100% working either way!
0
 
adatheladCommented:
Was quite a long post really wasnt it! At least we got there in the end.
Split the points however you feel is right, I don't want to influence you. I won't complain :)
0
 
ispalenyCommented:
adathelad,
I did some calculations.


set dateformat mdy
go
select uid,EP,SubRate,Rate,convert(varchar,HitDate,111)
--uid,EP,SubRate,Rate,HitDate
from (
select uid,EP,SubRate=Rate-Rate_ispa,Rate
,HitDate=case when Rate<Rate_ispa
         then convert(datetime,convert(float,getdate())+(EP-EP_ispa)/(Rate_ispa-Rate))
         end
from
(
select x.uid,x.EP,y.EP as EP_ispa
,Rate=convert(float,x.EP)/(convert(float,getdate()-x.Created))
,Rate_ispa=convert(float,y.EP)/(convert(float,getdate()-y.Created))
from
(
          select 'angelIII' as uid,convert(datetime,'03/09/00',0) as Created,497075 as EP
union all select 'nigelrivett'   ,'09/04/00' ,243957
union all select 'ScottPletcher' ,'09/10/01' ,172421
union all select 'bhess1'        ,'11/17/98' ,159295
union all select 'chigrik'       ,'11/13/98' ,131519
union all select 'tchalkov'      ,'04/10/98' ,111901
union all select 'simonsabin'    ,'03/03/99' ,103532
union all select 'acperkins@devx','05/29/01' ,87370
union all select 'miron@devx'    ,'08/24/01' ,85623
union all select 'spiridonov'    ,'08/13/97' ,75921
union all select 'gpbuenrostro'  ,'07/29/99' ,50601
union all select 'adathelad'     ,'10/11/02' ,47951
union all select 'spcmnspff'     ,'09/17/01' ,46730
union all select 'emoreau'       ,'08/02/98' ,41595
union all select 'curtis591'     ,'02/16/00' ,33098
union all select uid='ispaleny',Created =convert(datetime,'12/25/02',0) ,EP= 20046
) x
cross join (select uid='ispaleny',Created =convert(datetime,'12/25/02',0) ,EP= 20046 ) y
) xx) xxx
order by isnull(HitDate,'9999') DESC, case when HitDate is null then -SubRate end
--order by Rate DESC

0
 
ispalenyCommented:
Replace getdate() by @getdate and add
declare @getdate datetime set @getdate='03/14/03'
if you want to get correct results.

You are going ahead.
Good luck!
0
 
ispalenyCommented:
hanneman,
21 pages, it is really long. I wonder it resulted in a running system. I do not care about points, although it is a nice game. Split the points of your own free choice.

Never spend more for an acquisition than you have to.
(Ferengi - Rules of Acquisition)
0
 
hannemanAuthor Commented:
You coulda told me that before i kept upping the points!!!
Oh well, easy come easy go.


BTW, SQLserver will not start now:

C:\PROGRA~1\MICROS~4\MSSQL\BINN>sqlservr.exe
2003-03-19 14:28:03.54 server    initerrlog: Could not open error log file 'C:\Program Files\Microsoft SQL Server\MSSQL\LOG\ERRORLOG'. Operating system error =
32(error not found).

hahahahaha! well, I think I have enough here for me to re-install anyway....


0
 
hannemanAuthor Commented:
it was running already. machine is being disobedient and not showing service manager. everything ok.
0
 
MindphaserCommented:
hanneman

You asked to split points between ispaleny (250) and adathelad (100)

I have reduced the points on this question from 350 to 250 as indicated by your request at Community Support. Please copy the URL and create a new question in this topic area for the other Experts to whom you wish to award points. The title of the question should read "Points for", followed by the Expert's name. In the question itself, you should paste the link to the original question and perhaps a comment stating that the points are for their help with that question. Once you have created the new questions, you can go back to the original, and accept the comment from the Expert for whom you did not create a new question. The Experts will  comment in your new "Points for" question(s), which you then accept and grade to close.
If you have any questions, please don't hesitate to ask.
Thank you.

** Mindphaser - Community Support Moderator **

I left instructions on how to proceed in the original question.

** Mindphaser - Community Support Moderator **
0
 
hannemanAuthor Commented:
Thanks heaps to ispaleny, adathelad and EE. I widh I could give A+!!
0
 
ispalenyCommented:
Thank you.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 40
  • 30
  • 19
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now