We use SQL server 2008 as our RDBMS and we have a database that has a different user rather than dbo as its owner.
The problem is in one machine a stored procedure can not run unless its owner is mentioned.
If we connect to our database using this user and try to execute the following :
exec ourSP
we get a "could not find ourSP" error but this works fine:
exec user.ourSP
Does anybody knows what can lead to such a strange behavior?
Check the default schema of the user under which the connection is being made - possibly using the query
select default_schema_name from sys.database_principals where name = '~user_name~'
Objects are searched for in the user's default schema and then dbo. No other schemas are considered unless you name them explicitly, as you do in your second query.
On this basis, it seems likely that a) the user isn't set up correctly or b) you're not connecting as the user you expect.
It seems that If a user is checked as SysAdmin in sever security area it will have the dbo user name when a connection to a database is made and it will be treated like dbo.