Tables and Views with other prefix than dbo.**

When a Table or View is added to a database, it is normally called something like:
dbo.<tablename>
dbo.<viewname>

But on some Windows server these are added like:
<computername>\<username>.<tablename>
<computername>\<username>.<viewname>

What might cause this?
Stef MerlijnDeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
this means that the user creating the objects has db_owner permissions, and does not specify "dbo" when creating the objects.

to change the owner of the objects:
http://support.microsoft.com/kb/275312

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Stef MerlijnDeveloperAuthor Commented:
Why have Stored Procedures have a prefix of dbo.<spname> anyway?
Is this not dependent on the DB-ownership?
santhimurthydCommented:
If the User who creating table \ view having schema of his own name and then the objected created by the user will be with his default schema

check this Article
http://msdn.microsoft.com/en-us/library/dd283095(SQL.100).aspx

to verify what schema is the default schem of an user

i) Right clcik the User under the server level "Logins" in 'Security"
ii) Click the Properteis
iii) Select "User Mapping" in the Left hand menu
iv) Check whats the UserSchema and DataBase Schema
v) Change it to which schema you want it.
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

TempDBACommented:
there must not be any stored procedure created by those user. Or if there is, they might have created with dbo. and they have permission on the schema dbo too..
Stef MerlijnDeveloperAuthor Commented:
I've tried to change the Schema to "dbo" for the login-user on SQL Server.
But when saving I get an errormessage:
The DEFAULT_SCHEMA clause cannot be used wth a windows group or with principals mapped to Certificates or asymmetric keys.(Microsoft SQL Server, Error:15259)

Open in new window


How can I solve this problem?
Stef MerlijnDeveloperAuthor Commented:
By the way:
In User mapping the Default Schema for the database is empty.
Also DB_owner and public are checked for the database.
santhimurthydCommented:
Check whether the below discussion helps for you

http://social.msdn.microsoft.com/forums/en-US/sqlsecurity/thread/7d46a024-7ed5-4c9b-b091-3640dc04f5a1/

Meanwhile will try to simulate in my machine, Could you share as what the steps you have followed ended up in this error
Stef MerlijnDeveloperAuthor Commented:
Basically a windows user is added to a Windows group.
This means I can't assign a Default Schema to the Login user in SQL Server.
As it is not my own server I don't exactly know how things are setup.

One thing I did discovered:
Basically this is the very first thing angelIII: posted here.
When I add "DBO." to all databasechanges is works, like:
CREATE TABLE DBO.<mytablename>
CREATE VIEW DBO.<myviewname>
ALTER TABLE DBO.<mytablename>
ALTER VIEW DBO.<myviewname>

Stef MerlijnDeveloperAuthor Commented:
Thanks for the answer and sorry that it took so long, it slipped my attention.

The problem was indeed that I didn't specify in all cases "dbo.". I've updated my code and now everything works perfectly.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.