June 26, 2003 at 10:43 am
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
June 26, 2003 at 11:33 am
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?
-SQLBill
June 26, 2003 at 11:54 am
Since the login problem started happeneing when you failed the cluster over, I would check to make sure all of the client ODBC connection are going against the proper cluster resource (ie: against the virtual SQL Server rather than the primary node or the name of the cluster itself.)
For teh backups, do you have it setup as a mainenance plan, or was the wizard used in creating the job. If so, I would hardcode the backup in the job step detail.
Work like you don't need the money.
Love like you've never been hurt.
And Dance like no one is watching.
Work like you don't need the money.
Love like you've never been hurt.
And Dance like no one is watching.
June 26, 2003 at 12:03 pm
quote:
1st error message:SELECT permission denied on object 'ObjectName', database 'DatabaseName', owner 'dbo'.
The cursor was not declared.
The login used for your ODBC setup seems not have 'dbo' privilege or sufficient permissions to access database objects.
After detach/attach, your database may not be owned by 'sa' if you do not login as 'sa' to run the attach. Use sp_changedbowner to change it if database owner is not 'sa'.
June 26, 2003 at 12:05 pm
Everything worked fine until the database was moved to a new virtual server and a new array. ODBC connections worked before the move. And the ODBC connections don't change from one node to another anyways.
The backup is being run as a job step that I wrote. The step is:
backup mydatabase to dbdevice with init
Again...this worked before I moved the database to another virtual server.
Maybe this will help:
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
June 26, 2003 at 12:23 pm
quote:
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).
Change from default instance to named instance will definitly affect your ODBC setting that has to configured to point to named instance in format machinename\instancename or you created server alias for the second instance. I am assuming you have made the changes, otherwise, your application wouldn't work as you mentioned your users were able to access the data from one node.
I don't think backup failure is to do with what kind of instance running. You were tring to backup database to a device 'dbdevice' that has to be created in disks that disks resource must be in second virtual instance group.
June 26, 2003 at 12:42 pm
The backup is being done to a device in the new drive array. I recreated the devices (sp_addumpdevice) when I moved the database.
Also, the backup does work if I do it in QA or as a JOB if I run it as myself. It fails when it's run as a JOB using DBO.
This has really got me stumped.
-SQLBill
June 26, 2003 at 1:11 pm
Who is your job owner? If you can, try to take SQL Server Agent resource offline and online to see any help.
June 26, 2003 at 1:40 pm
I'm the job owner. I've stopped and restarted all services several times during the day while trying to solve this.
I'm going home soon (hopefully). I'm running the backups as me so I have backups working.
I just need to find out why they won't run under DBO and why my one database is inaccessable when I fail it over. I'm thinking it has to do with this DBO issue.
Can DBO get orphaned in a move? Can it be:
sp_revokedbaccess'ed and then added back it?
-SQLBill
June 26, 2003 at 9:30 pm
I usually change the SID in sysusers to x01 for the dbo name because it is usually the SID of the user that created the db in the first place. That fixes a lot of problems after moving a database.
June 30, 2003 at 3:36 pm
Check the port number for the named instance, it should be 1434. In sql client configuration create an alias name using port number.
July 1, 2003 at 7:25 am
Marcus,
The default instance gets the TCP Port of 1433. The next instance gets a different Port. My Ports are correct.
Here's the solution:
1. My database 'lost' it's owner in the move. I had to run sp_changedbowner 'dbo'.
2. Somehow on the one node, the ROLE I use lost all of it's members. Once I added everyone back to the ROLE they could access the data again.
-SQLBill
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply