Can't login to SQL Server

[SQLBill]

SQL Server 2000 SP 4.

I have three logins on SQL Server for this server.
1. Domain account with System Administrator role.
2. Domain account with db_datareader on one database
3. SQL Server account with db_datareader on one database.

Testing method.
Open Query Analyzer on my desktop and log into the server instance.

1. If I runas Query Analyzer using the account with SA priviliges, I can connect.
2. This login fails with Login failed for user ......
3. This login fails with Login failed for user ......

The domain controllers have been checked to ensure they are replicated.

There are no login failures in either the SQL Server Error Log nor the NT Event Viewer logs.

Why can only logins with System Admininstrator permissions access the databases and why aren't we seeing login failures anywhere?

We eliminated issues with passwords. I originally thought the domain controllers stopped 'talking' to each other or weren't replicating the active directory logins. But it isn't either of those causes.

I need some other suggestions on what to check.

Thanks,

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[gmmastros]

>> I need some other suggestions on what to check.

Perhaps this is a long shot, but it's worth checking.

Open Enterprise Manager
Drill down to your database.
Right Click -> Properties
Click 'Options' tab.

Under the 'Access' section at the top, is 'Restrict Access' checked?

I'm going to guess that you have... Restrict Access (Members of db_owner, db_creator, or sysadmin) selected.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLBill]

Good suggestion. But Restrict Access is not checked.

Also, to add, if I give the user SQL Server System Administrator permission, they can log in successfully.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[gmmastros]

I'm gonna guess that you don't want to give your users system administrator privileges.

I'm out of ideas. Sorry.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLBill]

Definitely not - but these are pre-prod testing environment so we are going with that solution til we figure out the answer. We don't care if these are 'broken/hacked' by the users as they are a stand-alone environment. If we can't find the solution, we are going to wipe the servers and rebuild them.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[SQLBill]

More weirdness.

Two servers. Same SQL Server account on both using windows authentication. Server1 login has been given SQL Server's system admin role. Server2 login has been given only db_datareader to one database.

I can access Server1, but not Server2. HOWEVER, if I use SSMS (SQL Server 2005) I can access BOTH. I just can't access them both via Enterprise Manager without having SQL Server system admin access.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[mrdenny]

Try setting up alaising to force TCP? Could be a strange NTFS/other OS rights issue on the named pipes connection.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog