June 5, 2003 at 4:11 am
Hi everyone - virgin poster - please be gentle!
I have restored a DB to a new server, sp_change_user_login is showing 0 orphans, however, when I log in as my main development user, who owns all objects in the database (except system dbo stuff), I have found that if I SELECT * from table_name, it fails to work - returning 'Invalid object name'. If I SELECT * from owner.table_name, then the valid dataset is returned. This is causing major aggro because DTS, SP's all dont qualify the objects, as previously (old server) they didnt have to. Huge rewrite if I cant figure this out - please help if you can.
many thanks
June 5, 2003 at 5:31 am
When you run sp_helpuser in the database you restored, does it show associated login name for the user name?
June 5, 2003 at 5:36 am
My GUESS is that the login that owns the object is no longer the owner of the database. Being that it is no longer the owner of the DB, the default owner is now something different forcing you to qualify your objects in your select.
I have not tested this so, it is just a theory / guess.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
June 5, 2003 at 6:45 am
Hi
sp_helpuser returns:
username groupname loginname defaultDB
dbo db_owner sa master
sysdba db_datareader NULL NULL
warehouse db_owner warehouse PfizerDW_SI
the user I'm having problems with is Warehouse. This looks OK to me (but I'm no expert)logged on to DB as 'warehouse' and I have to qualify warehouse owned tables.
To prove they do belong o the correct owner, I've run:
SELECT
sysobjects.name AS [TABLE_NAME],
sysusers.name as
FROM
sysobjects INNER JOIN
sysusers ON sysobjects.uid = sysusers.uid
WHERE (sysusers.name = 'warehouse') AND (NOT (sysobjects.type = 'D'))
and the returned dataset lists all the tables that I would expect to be owned by 'warehouse'
any other tips?
thnx
June 5, 2003 at 6:58 am
I would try to change the owner of the database using sp_changedbowner stored procedure.
EXEC sp_changedbowner 'username'
If this does not work, you can try to:
-script all objects in the database
-drop the user
-drop and recreate login
-recreate a user, make it a db owner
-recreate objects.
Hope this helps.
June 5, 2003 at 7:05 am
I've already tried sp_changedbowner, but as the user warehouse already exists - i get 'The proposed new database owner is already a user in the database.'
I will try your proposed method - thankyou
June 6, 2003 at 6:32 am
I have similar problems before, you could detach and attach that DB to fix the ownership by select warehouse as db owner from Enterprise Manager of SQL 2000.
quote:
I've already tried sp_changedbowner, but as the user warehouse already exists - i get 'The proposed new database owner is already a user in the database.'I will try your proposed method - thankyou
June 6, 2003 at 7:22 am
Guys
thanks for all the input - it turns out that it is an sp_change_users_login fixable problem, but its all about the order in which events take place. The problem is now fixed.
Drop DB
Drop offending login
stop database
start database
recreate login and give permission to pubs
recreate DB
give public and dbo permission to recreated login
restore DB
sp_change_users_login 'update_one', 'login', 'password'
and all is well !!
thnx again
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply