Re: User ID issue - urgent

John,

To recap what I think you are saying:

1 - The server is set to SQL and Windows authentication, not SQL only.
2 - You have restarted the server since the authentication model was set.
3 - You are working with a restored database.
4 - You have dropped and recreated the ID login and the database several
times.
5 - You can only login using Windows authentication, despite the server
setting.
6 - However, despite point 5, you say this database is the only one for
which ID and PWD does not work.

Certainly, by now you are frustrated and none of us have been a big help so
far. So, using the list above a couple of actions to try:

A. Considering points 3 and 4:
If you did not do already, you FIRST need to drop the database _user_ named
"ID" BEFORE creating the SQL Server _login_ named "ID". Make the new "ID"
a database user and grant it the appropriate roles and rights.

This is because the restored database _user_ can have a SID that does not
match the SQL Server _login_ SID that you create with the same name.
(Unless you set the SID in the CREATE LOGIN statement.)

SQL Server Management Studio can make it look like the login and user are
linked, even though they actually are not because the SIDs are different..
(Probably something on the fix list for SSMS.)

B. Considering points 1, 2, and 5, if action A does not help:
Set a password for 'sa' that you can remember, change the server to SQL only
authentication mode, and reboot. See how connecting to SQL Server works for
you, SQL accounts, Windows logins, etc. Login as 'sa' then set the mode
back to SQL and Windows authentication, the reboot again. Now see what
happens when you try to login.

C. Considering point 6:
This only makes sense to me if this can be solved by Action A. But perhaps
I misunderstand what you were saying.

RLF

"John" <John@xxxxxxxxx> wrote in message
news:%23gPIREHcHHA.4656@xxxxxxxxxxxxxxxxxxxxxxx
I tried that and still nothing, and No I can only login using windows auth
and I have the option SQL and Windows auth checked but an actual ID and PWD
doesn't work for this one database


"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message
news:Xns98FFF3894D264Yazorman@xxxxxxxxxxxx
John (John@xxxxxxxxx) writes:
I get the error when i try to login to the db from my web app and when I
try to use query analyzer.

Did you try restarting SQL Server? As Sue said, you need to cycle the
server if you change the login options.

Can you log in with any SQL Server login?

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx




.

Relevant Pages

  • Re: SQL User SID format?
    ... When you move databases to another server, you can remap users to logins ... capability to remap a user to a login with new ALTER USER syntax - I ... The new ALTER USER syntax works for remapping both SQL and Windows ... this means you already have access to a SID from the syslogins catalog; ...
    (microsoft.public.sqlserver.security)
  • Re: SQL Server on XP Home Network
    ... The sa account is the system administrator "God" account witihin SQL ... This account is a SQL-Server login. ... You might be able to use Enterprise Manager to go in and create a new SQL ... Select the "SQL Server Authentication" ...
    (microsoft.public.sqlserver.setup)
  • Re: SQL Server 2000 Replication Agents Credentials
    ... the snapshot agent IS using sql login to connect to the ... The pull distribution agent IS using sql ... Do you know where SQL Server 2000 saves the sql ...
    (microsoft.public.sqlserver.replication)
  • Re: Can you update/process AS2005 cube with AMO and SQL authentica
    ... We have cases where the AS server and the Data Source server reside on 2 ... The data source is properly secured using SQL Server ... login and no serious client will accept the login with no password. ...
    (microsoft.public.sqlserver.olap)
  • Re: Q: Named pipes and Windows (integrated) authentication
    ... >By the way, if you don't have SQL Enterprise Manager, there's an MSDE ... >>>his windows authentication to connect to the server. ... >>>Creating databases is only acomplished after you've already connected. ...
    (microsoft.public.sqlserver.connect)