Database can't be reached.

[SQLBill]

SQL Server 2000 on a two-node cluster, mixed authentication

I moved a database from one server to another (detached it, moved it to the new drives, attached it, cleared orphaned logins, put proper logins back). Tested it and almost everything worked fine. My users can access the data, but my backup job keeps failing.

I failed the server and cluster over to the other node and now the users can not access the database (and it's only this one, they can access all others). I still can't back it up.

I see their logins in the Error Log and the OS Event Logs. I am not getting any error messages in either place.

The only error messages I see are on the user's workstation:
(I had to 'sanitize' the names used.)

1st error message:
SELECT permission denied on object 'ObjectName', database 'DatabaseName', owner 'dbo'.
The cursor was not declared.

when I select OK on that error, I get:
DatabaseName ODBC data source does not contain the ObjectName schema!!

Anyone have any idea of what happened/what I did wrong or didn't finish?

-SQLBill

 

[SQLBill]

I have another thread going that is related to this one... thread962-587111.

anyways...
Here's more information:

I have a job set up that backs up this database. It's set to run as DBO. It fails. I run it under QA as me and it succeeds. I change the job to set it to run as me and it works fine.

How do I get DBO to work on this database again? and could this have caused my problem?

-SQLBill

 

[SQLBill]

Here's more information:
The database I'm having problems with was on one virtual server default instance (VServer1). I moved it to another virtual server with a second instance of SQL Server(VServer2/MyInstancename).


-SQLBill

 

[SQLBill]

Here's the current status:

1. Got the issue with backing up the one database solved, see the thread that I ref'ed in this one.

2. Found we have a hardware problem that causes the cluster to failover all the time. It's not failing over to the other node, part of the 'fabric' fails over to the other part. We are trying to get a replacement part.

3. Solved this problem with users not being able to access the database when it was on the first node. Turned out to be a permissions issue. Somewhere along the line, the users were removed from a Windows group on node1. Because of all the issues we had with our system, we were using node2 all the time so we never noticed the permissions issue.

Except for the hardware issue(s), everything seems to be fine.

-SQLBill