September 21, 2005 at 9:09 am
Hi there,
I have this user(WindowsLogin), who has DBO access to a database. When he creates tables via OSQL the owner of the objects is not DBO, but his loginname. I wonder why creating an object with OSQL does this. When I am logged in with only DBO access to a database, I can create tables in EM and QA, and they all show up with owner DBO.
Is there any solution for this irritating hiccup? And BTW, this developer has no SQL Server utilities on his pc and will not get them.
Greetz,
Hans Brouwer
September 21, 2005 at 9:43 am
Tell him to create table like create table dbo.tablename
osql -SServername -ddatabaseName -Q" create table dbo.tablename ..." -E
it should work fine.
mom
September 21, 2005 at 9:44 am
R U a sysadmin? Sysadmins always create as dbo.
This user should use the dbo.tablename naming convention when creating tables to prevent this.
use sp_changeobjectowner to change owner to dbo.
From books online
When users access an object created by another user, the object should be qualified with the name of the object owner; otherwise, Microsoft® SQL Server™ may not know which object to use because there could be many objects of the same name owned by different users. If an object is not qualified with the object owner when it is referenced (for example, my_table instead of owner.my_table), SQL Server looks for an object in the database in the following order:
If the object is not found, an error is returned.
For example, user John is a member of the db_owner fixed database role, but not the sysadmin fixed server role, and creates table T1. All users, except John, who want to access T1 must qualify T1 with the user name John. If T1 is not qualified with the user name John, SQL Server first looks for a table named T1 owned by the current user and then owned by dbo. If the current user and dbo do not own a table named T1, an error is returned. If the current user or dbo owns another table named T1, the other table named T1, rather than John.T1, is used.
If a database object owner must be removed from a database, the owned objects must be dropped first or their ownership transferred to another user.
September 22, 2005 at 1:53 am
Tnx for the help, works like a charm
Greetz,
Hans Brouwer
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply