sp_MSdbuseraccess / has_dbaccess errors on hosted server

Question

sp_MSdbuseraccess / has_dbaccess errors on hosted server

rmilner

SSC Enthusiast

Points: 148
More actions
June 14, 2006 at 8:48 am

#91965
I’m experiencing some problems on a shared hosted SQL Server that is affecting the use of DTS and probably Enterprise Manager too. The situation and symptoms are as follows:
- We have a user “my_user” which has dbo access to our own database “my_database” on a shared, remotely hosted SQL Server
- On attempting to edit or execute a DTS package containing a single Copy SQL Server Objects task with a connection to my_database on the remote SQL Server, an error of the following form is received
- Microsoft SQL-DMO (ODBC SQLState: 08004) [Microsoft][ODBC SQL Server Driver][SQL Server]Server user 'my_user' is not a valid user in database 'NOT_my_database'.
On expanding the Database folder for the remote server in Enterprise Manager, it is empty except for the text “(No items)”
On refreshing the Database folder, it is correctly populated with the databases to which we have access on the server (master, msdb, my_database, tempdb)
The problem is intermittent, and the name of the other database given in the error varies between 3 of the 20 databases on the server.

This is causing problems because I’d like to use DTS to get a local backup of the database. I realise there may be other ways I could do this, but I have been investigating and have my suspicions about what the administrators of the server have done. I have found that the error occurs at a call to has_dbaccess(‘NOT_my_database’) within master..sp_MSdbuseraccess (as called within DTS, amongst others). This function should only return 0, 1 or NULL.

The KB article http://support.microsoft.com/default.aspx?scid=kb;en-us;315523 describes a variation of these symptoms, and gives the cause as the manual deletion of the guest user from sysusers. In my case the guest user exists in my_database..sysusers, but I have no way of knowing whether they have deleted it from sysusers in the 3 databases given in the error message. The providers of the service aren’t admitting anything for now!

This post is partly for information purposes, and partly to see if anyone knows of any other possible reason for this error. What’s the betting that the administrators have manually deleted the guest user in some databases? What else could it be?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

This is just a shot in the dark, but are any of the objects in the source database owned by my_user? Then, when it tries to create them at the destination, it may not be able to because the my_user user does not exist.

John

rmilner SSC Enthusiast Points: 148 More actions · Answer 2

Nice try, but all objects are owned by dbo. Besides, the error occurs on querying the available databases, well before any objects start to transfer.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

OK, I've read your original post a bit more thoroughly now. You're wondering whether the guest user has been deleted. We always delete it from our user databases, and even if we didn't, we wouldn't allow it to create objects, which is what it sounds as if you need to execute your DTS task. I think you're going to need to ask the owners to give you the access you need.

Good luck

John

rmilner SSC Enthusiast Points: 148 More actions · Answer 4

Careful what you say about DELETING the guest user! My theory is that all this was caused by the administrators manually deleting the guest user from certain user databases, rather than using sp_revokedbaccess or sp_dropuser.

If you do actually DELETE guest from sysusers, do you have a particular reason for doing it that way?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Different names for the same thing. If you go to Enterprise Manager and "delete" a user, SQL Server actually runs sp_revokedbaccess in the background to accomplish that.

I think your problem comes down simply to the fact that the administrators of the remote server haven't given you the access you need.

John

rmilner SSC Enthusiast Points: 148 More actions · Answer 6

No, they're not the same thing. As the MS article states, one of the conditions required to cause this is:

"The guest user account has been deleted from the sysusers system table in one of the databases with a DELETE statement issued directly against the system table."

Obviously there are DBAs who do this, and I was wondering why.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

I see what you're saying (and apologies for not reading through the MS article). Obviously I wasn't advocating messing with system tables. You can't really legislate for administrators of other servers that do this - especially if you're trying to connect to them!

John