If you're in an emergency situation and you need to move some databases from one SQL Server host to another, one of the myriad problems you'll face is how to preserve the login IDs associated with the databases so that your applications don't have to change. For example, lets say that you have an AdventureWorks database on Server A and you log in via SQL authentication using a userID AdvWorksUser, password Us3r_Pwd. Now, because of some terrible twist of fate, you need to move this database to Server B. So you either backup and restore the database or you stop the MSSQL service on Server A and copy the MDF and LDF data files across to Server B where you use
sp_attach_db to put the database online.
Bingo, Presto! You're database is online and you can log in via Query Analyser using Windows Authentication and run queries. Everything should work, right? Nope! You need to fix your user accounts. Server A had a login ID of AdvWorksUser. Server B might not have this user. So you have to add the user to Server B. Before you can just add your login and assign him rights to the database as a user, you've actually got to remove the old user from the database in question (in our example, the AdventureWorks database). Simply use Enterprise Manager to drill down to the database users section and delete the user from there. UNLESS! If you're use is the owner of any objects, including tables, stored procedures, user defined functions, etc, you'll get an error when you try to delete the user. Your user is identified as the owner of any objects created while logged in as that user. You'll have to associate those objects with the dbo user (required for every database) before you can delete your AdvWorksUser account. You can actually see these assignments by looking in the sysobjects system table. The dbo user is usually uid=1. However, you can't simply run an update statement on sysobjects. Go ahead and try, SQL Server won't let you do it. You have to use a command to change the owner.
sp_changeobjectowner. You simply put in the name of the object to change and the name of the new user - sp_changeobjectowner 'GetMySalesSProc', 'dbo'. That's it.
Easy to do, unless you have hundreds of objects. If you have hundreds of objects to change owners for, you'll want a script. Follow the advice in
this blog entry from
Scott Forsyth and use the following script to rename tables and stored procedures. User defined types and functions are not handled here, but are easy to do by hand.
DECLARE @old sysname, @new sysname, @sql varchar(1000)
SELECT
@old = 'oldOwner_CHANGE_THIS'
, @new = 'dbo'
, @sql = '
IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES
WHERE
QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''
AND TABLE_SCHEMA = ''' + @old + '''
)
EXECUTE sp_changeobjectowner ''?'', ''' + @new + ''''
EXECUTE sp_MSforeachtable @sql
-------------------------------
The
same can be done to stored procedures. This example works differently
though. It doesn't actually make the change. Save the results in text
and then paste the result back into Query Analyzer and run it.
-------------------------------
DECLARE @oldOwner sysname, @newOwner sysname
SELECT
@oldOwner = 'oldOwner_CHANGE_THIS'
, @newOwner = 'dbo'
select 'EXECUTE sp_changeobjectowner '''+QUOTENAME(a.SPECIFIC_SCHEMA)+'.'+QUOTENAME(a.ROUTINE_NAME)+''','''+@newOwner+''''
from
INFORMATION_SCHEMA.ROUTINES a
where
a.ROUTINE_TYPE = 'PROCEDURE'
AND a.SPECIFIC_SCHEMA = @oldOwner
AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(a.SPECIFIC_SCHEMA)+'.'+QUOTENAME(a.ROUTINE_NAME)), 'IsMSShipped') = 0
Once you run the above script, you can go into Enterprise Manager and delete the old user. Then add your new SQL Server login, assign it rights to the database, and you're good to go. I hope you stored those passwords somewhere!
-- Matt Ranlett