Monday, February 13, 2012

All-of-a-sudden, I get "Invalid object name"

I have been developing a database in SQL Server Management Studio Express for the past few weeks. I have been writing stored procedures and functions, creating and deleting tables, etc., etc. and everything has been working just fine.
Today, however, if I try to run any of the functions I have written I get "Invalid object name 'functionName'."
This occurs even if I create a new function, execute the CREATE statement. Then if I refresh the Functions folder I can see my newly created function, but I cannot run it.
This is terribly frustrating.

Does anyone have any ideas as to what might have changed? I am logging in as the same user, on the same machine that I have always logged in on. I created the database that is giving the error.

When I right-click SQLEXPRESS in the object browser, and go to Permissions in the Server Properties dialog, no permissions are selected as "granted" for my login name under any of the Logins/Roles. (ex. BUILTIN\Administrators: my login name is listed in the "Explicit permissions for BUILTIN\Administrators" list, but no permissions are granted. If I go down the list selecting [checking] the various permissions, then close the dialog. When I open the dialog again, rows have been added to the list that have the Permission name, then "sa" listed as the Grantor, with that row checked. But, there are still no checks next to my login name.)

The only thing that has changed on my machine, that I am aware of, is that a Windows Authentication update ran on my machine earlier today. I am using Windows Authentication in my SQL Server instance. I assume it has something to do with that.

Please help. This is urgent. We have a presentation on this project in 3 days.
ugh.

Thanks in advance for any help you can provide.

hi,

I'd check that your database users' (database principal) default schema is still the one you are used to have too...

and obviously I'd verify all your code includes full object references in the form "schema_name.object_name" and not only

CREATE PROCEDURE usp_ProcName

....

EXEC usp_ProceureName

but

CREATE PROCEDURE the_schema.usp_ProcedureName

....

EXEC the_schema.usp_ProcedureName

and the like..

regards

No comments:

Post a Comment