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].fi
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?
For example: [test_data].[test_data].fi
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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...
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...
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.
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'ASKER
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.
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.
have you check/confirmed any batch processing which maybe running for the database?
ASKER
@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.