October 12, 2011 at 4:16 am
Hi All,
We have a SQL 2000 database where the identifier contains DB Owner name.
When we are running select query without the db owner in identifier it is returning an error invalid object. When we put the identifier the query runs fine.
Please note that we have logged in with db owner credential.
Eg: select * from table
Error: Invalid object
select * from dbowner.table
returns result.
When I tried to delete the user after removing the DB Owner role it is ging an error user owns object in database and cannot be dropped.
Can anyone please help to sort out the problem?
How can we remove the ownership of these objects to delete the user?
October 12, 2011 at 5:50 am
change the owner of the objects owned by that user.
sp_changeobjectowner 'tablename','dbo'
When your objects are owned by dbo, you do not need to prefix this owner. For any other owner, you'll need to prefix owner.objectname
Also, if any user owns any object, you wont be able to drop that user. Either change ownership (as shown above) and then drop the user OR drop all objects owned by that user and then drop the user.
October 12, 2011 at 9:48 pm
Thanks Cinyaz for your help.
There are some objects owned by dbo and some others by the user mentioned.
As per requirement the stored procs must only be excutable when we login with the particular credentials.
The database is an old database with 2500+ objects owned by the user.
It used to work but has stopped working recently.
I have already moved the object ownerships to dbo, deleted the user login and created it from scratch. Then I changed the ownership back to the user.
Still it is not running.
Please let me know if there is any solution available to it.
October 13, 2011 at 12:47 am
Please post the sql that you are trying to execute and the error message that you are getting.
October 13, 2011 at 1:12 am
The following is the query.
Eg: select * from table
Error: Invalid object
select * from dbowner.table
returns result.
I want the query to be executed without using the dbowner in the identifier i.e. to use in the form
select * from table
instaed of
select * from dbowner.table
October 13, 2011 at 6:40 am
Knowledge Hunter (10/13/2011)
The following is the query.Eg: select * from table
Error: Invalid object
select * from dbowner.table
returns result.
I want the query to be executed without using the dbowner in the identifier i.e. to use in the form
select * from table
instaed of
select * from dbowner.table
As mentioned earlier, you will need to prefix the owner name with the table name if the owner is not dbo.
Having said that, if you login using dbowner login id (as per your example), You will be able to access the table without prefixing dbowner.
If i create an object, I will be the owner of the object. I can use that object without prefixing my user id.
However, if someone else is trying to access my tables, he/she will have to prefix the owner id (my user id).
If you want any user to access tables, without using prefix, change the owner of the objects to dbo.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply