owner of objects is logged in but cannot see objects

Last Post 20 Jun 2006 09:42 AM by dkrogers55. 5 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
dkrogers55
New Member
New Member

--
12 Jun 2006 07:51 AM
I have a user (tn) that is dbowner on a database on a production site. This user owns nearly all the sprocs, tables and udfs in the database. We have backed up and restored this database from our production site to development and qa servers with no problems in the past (each server has the same SID for the tn user). Also, if we log into Query Analyzer (QA) as that user, we can execute any stored proc without specifying the owner (tn) explicitly and it works fine on the production site (EXEC usp_agenda_list, where the tn user owns this proc). Running Exec tn.usp_agenda_list works fine also.

We have a new server we have created the tn user with the same SID as everywhere else. We resoted the database to this server, and now when we log into QA with the tn user (it has a different password than production, but the same SID), we log in successfully, but HAVE to add the owner qualifier to get the usp_agenda_list sproc to run. When we run it without the owner specified, we get the following error: Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'usp_agenda_list'.

Sorry for the lengthy explanation, but wanted to make sure I was clear on the problem and the obvious SID question.

dkrogers55
New Member
New Member

--
12 Jun 2006 11:15 AM
User tn is a member of db_owner. SA is database owner as far as I know. Do not know which system table or information_schema view contains the current owner of the database.
dkrogers55
New Member
New Member

--
12 Jun 2006 11:42 AM
sorry...same setting on all servers
dkrogers55
New Member
New Member

--
12 Jun 2006 12:34 PM
Ran sp_change_users_login 'auto_fix','tn' and got this response.
The number of orphaned users fixed by updating users was 0.
The number of orphaned users fixed by adding new logins and then updating users was 0.

I already checked SIDs in sysusers and syslogins and they are the same value.
select * from sysusers where name = 'tn' --0xE555EA3282BFB844B1AFE0509AE180AC
select * from master..syslogins where name = 'tn' --0xE555EA3282BFB844B1AFE0509AE180AC

whynot
New Member
New Member

--
16 Jun 2006 07:46 AM
Is this new server a SQL 2005?
dkrogers55
New Member
New Member

--
20 Jun 2006 09:42 AM
sql2000. Finally gave up on this and detached all the databases this user owned objects in. Then deleted the user and added it back (with the same SID as before). This resolved the problem, which was obviously something wrong with the login itself. Thanks for the research on this.


Acceptable Use Policy
---