I am not so familiar with SQL Server 2005, but the problem is that when i created a vies and try to save it, instead of the name starting with the dbo. prefix, its starting with my username.
And i just found out that this is not happening only in the view its happening where ever i created an object and try to save it.
How can i change this from my username to make it display dbo. instead.
I think this is why i'm getting permission error when i try to use DTS package.
IF you want the dbo schema to 'own' the object (VIEW, TABLE, etc.) then use 'dbo.' as part of the name when you create the object.
CREATE VIEW dbo.MyView
AS ...
In order to correct the existing 'mis-named' objects, you will have to 'transfer' them into the correct schema.
ALTER SCHEMA dbo TRANSFER YourName.MyView
When that is complete, each item will then be in the [dbo] schema, and can be used by all users of the database, and referred to as dbo.MyView, dbo.MyFunction, dbo.MyTable, etc..
|||Thanks for the reply
We dont create them coding. This has to do with Cranberry, so when we create rules, we use them in cranberry.
The way we create a view is just to right click on the View, select new rule. Then after adding table and doing all modification then we click the save button. A box pop showing View1 that is what we change to the new name we want to use then after i save and went back to the view list, i see that it has added my username as a prefix instead of dbo.
So its not that I'm coding anything manualy, so i dont know where to put all the codes above.
Thanks.
N.B. Sorry, this is my 1st time using SQL.
|||Add the 'dbo.' to the name when you name the view.
For the existing 'mis-named' views, if you can't execute the code I presented previously, you will have to delete the existing views and re-create them and use the correct names (including the prefix).
|||Hi Arnie,
When i created another view and add the 'dbo.' to the name, it gave an error message
"Invalid Indentifier dbo.tr_XXXX_XXX_XxxXxx"
Thanks.
|||Hi Kaybal,
Modify the user that your application uses to have default schema of dbo. and make sure your user account has permissions on the dbo schema.
try this and let us know how you got on.
regards
Jag
|||Hi Jag,
Thanks for the reply, can you put through how to modify the user and have default schema of dbo. Where do i go or what link should i use
thanks
|||Hi Kaybal,
here you go. Let us know how you got on.
USE database_name; ALTER USER user_name WITH DEFAULT_SCHEMA = dbo; GOregards
Jag
