Link to home
Create AccountLog in
Avatar of ca_roland
ca_roland

asked on

Can not access the DB object any more while they are listed

Experts :

I have a login name : UserDDO which is mapped to SQL user DDO

There are tons of DB Obects owned by DDO, like DDO.tbl_Employee  and DDO.sp_createEmp etc.

Just recently, the UserDDO lost all the access to the DDO objects, I realized from a DTS fails.

Then I want to SQL Query analyzer and create connection with UserDDO/password

Then run sp_createEmp , I got a sp does not exist response.

If I use DDO.sp_createEmp, then I got sp_login is not found error, since sp_createEmp is depends on sp_login.

I checked everything, User permission on the DB instance, etc all the DDO.sp_xxxx are checked as EXEC

I even run the sp_change_user_login 'Update one', 'DDO', 'UserDDO' under sa login, it responsed with 0 was updated, that means, I believe, DDOUser to DDO map is intact.

Everything was working fine in the past and this happens just all of a sudden

Pls let me know should you need more info.


Thanks


Roland

Avatar of ca_roland
ca_roland

ASKER

as i do user_name(), the userDDO login return 'dbo'  !!!

but when i do sp_helpuser , i have DDO to userDDO mapping!!

sp_change_user_login 'Update one', 'DDO', 'UserDDO'  return 0 updated


please help

hmmm, if user_name() returns dbo, this means the current user is a member of the db_owner role, or in fact, might even be the actual owner of the database.

Did you run sp_changedbowner by any chance ?? This would 'move' all objects from the DDO user to dbo and hence make the current DDO objects 'disappear'

How many rows are returned when you do this :

SELECT name, uid FROM sysobjects WHERE name = 'sp_createEmp'

Also : have a look at what this returns to get an idea whether DDO was made dbo or not...

SELECT name, uid, suser_name(sid) FROM sysusers
ASKER CERTIFIED SOLUTION
Avatar of ca_roland
ca_roland

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Forget to thank you for the input. However, change the owner is not an option in my case
by the way, you shouldn't name your stored procs starting with "sp".  This is what the documentation says:

It is strongly recommended that you do not create any stored procedures using sp_ as a prefix. SQL Server always looks for a stored procedure beginning with sp_ in this order:

The stored procedure in the master database.

The stored procedure based on any qualifiers provided (database name or owner).

The stored procedure using dbo as the owner, if one is not specified.
Therefore, although the user-created stored procedure prefixed with sp_ may exist in the current database, the master database is always checked first, even if the stored procedure is qualified with the database name.

Important  If any user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.
Closed, 500 points refunded.
Computer101
EE Admin