Clicky

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?

asked 12/04/2011 11:36

btetlow-expert's gravatar image

btetlow-expert ♦♦


8 Answers:
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:
 
1:
2:
3:
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'

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.
link

answered

Rimvis's gravatar image

Rimvis

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...

link

answered 2011-12-04 at 23:53:24

Lowfatspread's gravatar image

Lowfatspread

@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.

link

answered 2011-12-05 at 03:02:20

btetlow-expert's gravatar image

btetlow-expert

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.
link

answered 2011-12-05 at 04:31:47

ScottPletcher's gravatar image

ScottPletcher

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...

link

answered 2011-12-05 at 11:56:08

Lowfatspread's gravatar image

Lowfatspread

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.
1:
2:
3:
4:
5:
6:
7:
8:
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'
link

answered 2011-12-05 at 12:26:25

ScottPletcher's gravatar image

ScottPletcher

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.
link

answered 2011-12-05 at 12:55:38

btetlow-expert's gravatar image

btetlow-expert

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

answered 2011-12-08 at 08:28:06

Lowfatspread's gravatar image

Lowfatspread

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×8

Asked: 12/04/2011 11:36

Seen: 316 times

Last updated: 12/08/2011 12:28