We help IT Professionals succeed at work.

A previous "designer" changed the default "dbo." prefix in SQL

I have a database where the original person that set it up, somehow created the tables within the DB to prefix with a name instead of the usual "dbo".

For example:    [test_data].[test_data].field1
Instead of the usual:    [test_data].[dbo].field1

Note the DBname is actually in twice if you follow the usual entry.  I'm comparing this to all my other databases that follow the usual convention of DBname.dbo.fieldname.

This became a problem when the main system this was installed on failed, and I've had to restore it to a new system using the backups from SQL.   The .NET code is still the same as original.

I'm having issues gaining access to the DB thru a .NET app, and in the process I discovered this prefix change was done.  Data is perfectly fine when using SQL itself.

I was getting an ambigious error message about a missing table, but in working thru the error code generated, it's actually complaining that it can't access ANY tables because they're reported as invalid objects -- don't exist or corrupt.  Of course they don't exist!   If you try to open a table with just the table name itself, it assumes (essentialy) "dbo", not a foreign name.

I took the particular error test code and ran it in SQL directly, and this is how I got to the prefix issue.   Change the prefix to use what's ACTUALLY in the DB instead of it "assuming" the dbo, and it works properly.

It's way too big of an app to change the code, and I've not found anything in the code itself that places this prefix as a value to make use of it, so it seems to me that the change has to be made in the DB itself....

Any ideas?

Is there a programatic way to change it back?   Can't change it at the table levels using SSMS as that prefix "hides" itself, so I figure it's stored elsewhere.

Where is this non-standard entry actually being stored, and is it something I can actually change?

Comment
Watch Question

Commented:
Hi btetlow-expert,

The "prefix" you are talking about is called "schema" in SQL Server. You can change a schema for tables (and other DB objects) by using ALTER SCHEMA command. The following script will generate "ALTER SCHEMA" statements for you:
 
SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + t.Name 
FROM sys.tables AS t INNER JOIN sys.schemas AS s on t.schema_id = s.schema_id 
WHERE s.Name = 'test_data'

Open in new window

You can execute it in SSMS, copy generated result, paste it back to SSMS and execute again. You you have other objects (views, procedures etc.), you should change "sys.tables" table into appropriate one.
Top Expert 2011
Commented:
it sounds like you've recreated the application userid and not set up the assocaited default schema for it. so its now using
the default dbo rather than the correct test_data schema which was intended.

check your documentation, if the app function with these named schema , then it must have been done in that manner for a specific reason...

Author

Commented:
@rimvis - I believe this may be what I'm after -- I'll test it and see where it gets things.

@lowfatspread -  Actually I should say the default user and scheme were within the DB, and I created a matching login user which was not there.

For the app however there is no specifically coded entries that demand it.   In fact, there are 2 other apps that use the same base code that do not use a specific scheme that matches, and they work quite well.

I suspect this unique prefix(schema) is a product of the designers inconsistent coding between 3 similar applications (this seems the general problem with their coding overall) --- they all share exactly the same code base, and the same DB structures....except for this one which deviated from the norm by having a specific name associated with it.

Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
You can change the default schema for the users in the "test_data" db only to be "test_data" instead of "dbo".  That will to be transparent to other dbs and their uses.
Top Expert 2011

Commented:
i think changing the default schema for the users in the test_data db will be the simplest change in the long run..

if you haven't consulted your backup/restore documentation prior to recreating the users/database to deal with this issue
its possible that other issues will come back and bite you at some stage....

it is a perfectly valid option to use schema names, and can lead to improved security , for all that its not a very common situation, and usually is the result of an "error/mistake" when establishing the users in the first place (or a product of a database migration (e.g. sql 7 --> sql 2005). However if your system has been functioning correctly up to now with these other schema names , then it must have been accepted as such in the past... good audit/maintenance policy should be to restore the system to the way it currently operates with minimal change...

any investigation/proposed changes of this nature should go through your normal software development "rigrous" change processing....

change the use schema defaults and update the documentation...

Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Code below will generate the commands to change the default schema (code does NOT run them; you must copy the output from the SELECT below to a new query window and run the commands).

Be sure to review the code before running, just to be sure.
SELECT
    'ALTER USER [' + CAST(dp.name AS varchar(128)) + '] WITH DEFAULT_SCHEMA = ''test_data'';  ' +
    '--original default schema was: ' + CAST(COALESCE(dp.default_schema_name, '<NULL>') AS varchar(128))
FROM sys.database_principals dp
WHERE
    dp.type NOT IN ('C', 'G', 'K') AND
    dp.is_fixed_role = 0 AND
    dp.name NOT IN ('guest', 'INFORMATION_SCHEMA') --'public'

Open in new window

Author

Commented:
The resolution ended up being a mixed solution really.  

In the end, the results were the unique prefix got changed to DBO like all the other databases of the same genre, and the user account & scheme were recreated.

Thanks all for your input to the issue....  Now, if I can only not find anymore non-standardized creations I will be only too happy.
Top Expert 2011

Commented:
have you check/confirmed any batch processing which maybe running for the database?