May 17, 2011 at 9:10 am
Hello everyone,
I think I know what I am going to do, but I'd be crazy not to run it past you guys first, just to make sure.
I am moving a database across to a new 2008 server from a 2005 server. Previously, the users and the database were on a SQL 2000 machine (before I came along)and when they transferred it all across the old SQL 2000 schemas went along for the ride. The schemas are inside the database itself under security.
Now, I do not want to take those old schemas across from SQL 2005 to 2008, there is a schema for every SQL login that uses the database (same name as the user), they (the user) is the owner of their schema and is of course their default schema.
I am wondering what approach to take, I plan to make the default schema of all the SQL logins 'dbo' (either before or after the transfer) will this be enough to make those old schemas a thing of the past? I have had a look at the database, and all the objects within it are owned by 'dbo' so I do not envisage any ownership issues.
Does it sound like I am going down the correct path? Can anyone recommend a better alternative?
Thank you for taking the time to read my post.
Kind Regards,
D.
May 17, 2011 at 10:02 am
Duran (5/17/2011)
Now, I do not want to take those old schemas across from SQL 2005 to 2008, there is a schema for every SQL login that uses the database (same name as the user), they (the user) is the owner of their schema and is of course their default schema.I am wondering what approach to take, I plan to make the default schema of all the SQL logins 'dbo' (either before or after the transfer) will this be enough to make those old schemas a thing of the past? .
You could Programmatically change the schema for objects or perform it manually but you may break a lot of code.
Whether or not you move or what schema you change to would depend on your situation.
I assume that you are going to start out in the Development Environment?
You probably may want to assess the impact that this will have. I would guess that it may depend on the schema and does the code hard code the username.objectname.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 17, 2011 at 10:16 am
Hello,
Firstly, thanks for answering. Yes, its in a dev enviroment, I believe that most, if not all the code is off-the-peg meaning standard applications bought off the shelf, I do not think anything has been developed in house (is that what you meant?)
Regards,
D.
May 17, 2011 at 10:28 am
Duran (5/17/2011)
I believe that most, if not all the code is off-the-peg meaning standard applications bought off the shelf, I do not think anything has been developed in house (is that what you meant?
I did not know if you users created a lot of adhoc queries or whatever using something other than dbo.
You could easily change the schema with a script using the INFORMATION_SCHEMA.TABLES.
I did not write the following script (steve schofield):
declare @OldOwner varchar(100) declare @NewOwner varchar(100) set @OldOwner = 'OldOwner' set @NewOwner = 'NewOwner' select 'sp_changeobjectowner ''[' + table_schema + '].[' + table_name + ']'', ''' + @NewOwner + ''' go from information_schema.tables where Table_schema = @OldOwner
So if you and start by performing some business analysis,convert the Databases, and perform a comprehensive UAT, you should find out what you are up against.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply