Too many objects in database(?)
-
I'm having a problem with a database, that doesn't seem to be running correctly any more. When I connect to the database via ODBC the application wants to start a stored procedure: DCRM_CreateIndex.
The ODBC connection is using NamedPipes. The login, in the DSN, is the owner of all the tables and procedures (except system tables and procedures). The error message I get back is:
SQL Error State: 42000,
Native Error Code: AFC,
ODBC Error: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure 'DCRM_CreateIndex'
(When resolved to the SQL Error messages, the correct error numbers are 2812, 16)
The procedure does exist and it is owned by the DCRM User.
The whole database was running just fine up to Friday last week. Today (Monday) I had a "supsect" database, which I fixed by restarting the MSSQL Server service. There were no entries in the SQL Error Log.
I tried restoring the database from last Friday, but that didn't help. I went back till last Wednesday with the same results.
Now I have read, that MS SQL behaves strangely when a certain amount of tables are created. (a couple of hundred). Well we've got around 360 tables in this database, but I couldn't find the documents referring to this problem with MS SQL.
My question: Has my problem something to do with the number of tables and procedures in the database? Or does my problem lie somewhere else?
I'd be thankful, if you could point me in the right direction.
Cheers, hot2use
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find. -
Have you tried connecting as and Admin account and then DCRM account in QA or EM to see what happens? If no difference run DBCC CHECKDB (see BOL) to check the database for problems.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
Now that's when the real fun things happened. I could connect to the database with QA using the DCRM user or the sa user. No problems at all. The objects were all there and I was able to query the tables and run the sprocs.
I ran the DBCC checkdb and everything was fine. I also ran the sp_change_users_login to check for orphaned users. Nothing there.
In the meantime I received a solution to the problem, but I'm not satisfied with it. I ran a script that changes the owner of all database objects to dbo, which formerly belonged to the DCRM user. Then I assigned the DCRM user the databaseowner role. I can now reconnect to the database.
So I'm assuming somewhere along the line, that the database (probably in the system tables) "lost" the ability to retrieve/resolve the owner information for the DCRM user. Is there a SID or a ROWGUID that could have started conflicting?
As I wrote before: The problem is resolved, but now I've got a user that can bypass the "DBO only" setting.
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply