SQL Table Ownership Changes, Quick and Easy

re: SQL Table Ownership Changes, Quick and Easy

Papayjl — Sun, 17 Jun 2007 09:34:39 GMT

<a href= monophthongizesardin.pochtamt.ru >lynn doyle</a> <a href= accommodatingdo.pochtamt.ru >wgn</a> <a href= http://lyonsredint.pochtamt.ru >restaurants in whittier 90601</a> <a href= bizarredirectre.pochtamt.ru >spa at the avenue baltimore</a> <a href= hemlocktriangular.pochtamt.ru >piliceimpounds</a> <a href= http://parameterp.pochtamt.ru >nude beaches new york</a> <a href= http://theat.pochtamt.ru >refurbished canon pixma</a> <a href= publishinggentleness.pochtamt.ru >wedding on video</a> <a href= corgipondage.pochtamt.ru >catholic jewelry</a> <a href= aquiferoussupport.pochtamt.ru >shopzilla</a>

re: SQL Table Ownership Changes, Quick and Easy

Bill S — Tue, 05 Jun 2007 14:16:09 GMT

Now that is a slick script. Thanks much!

re: SQL Table Ownership Changes, Quick and Easy

Shridhar — Thu, 05 Apr 2007 14:01:03 GMT

Saved my day yesterday! I had to tweak it a bit to work for views ... no problems.

re: SQL Table Ownership Changes, Quick and Easy

AZ — Sat, 10 Mar 2007 10:36:04 GMT

Great! Works just like a charm!

re: SQL Table Ownership Changes, Quick and Easy

soumya — Fri, 02 Feb 2007 11:55:00 GMT

great article. thanks!

re: SQL Table Ownership Changes, Quick and Easy

Rick T — Tue, 02 Jan 2007 20:52:22 GMT

Thanks for the tip on the system sproc Scott. Most helpful!

re: SQL Table Ownership Changes, Quick and Easy

Darran — Thu, 30 Nov 2006 11:53:03 GMT

I have adjusted the code slightly for a SQL 2005 version. I'm not a SQL expert, so I'd welcome comments if this code will work as I'd expect.

DECLARE @old sysname, @sql varchar(1000)

SELECT

@old = 'oldOwner_CHANGE_THIS'

, @sql = '

IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES

WHERE

QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''

AND TABLE_SCHEMA = ''' + @old + '''

)

ALTER SCHEMA dbo TRANSFER ?'

EXECUTE sp_MSforeachtable @sql

re: SQL Table Ownership Changes, Quick and Easy

Scott Forsyth — Tue, 03 Aug 2004 22:56:00 GMT

Hesky. I can't answer that right off. You can do it from Enterprise Manager by starting the process of detaching a database and killing all sessions. But from SQL directly, I would have to look it up. I don't know right off.

re: SQL Table Ownership Changes, Quick and Easy

Hesky — Mon, 26 Jul 2004 18:36:00 GMT

Could we also use this script if users are using the table?? SO what i want i to change the ownership when also the users are using this table in the application. Is there a possiblity drop all users which are connecting to these table??

re: SQL Table Ownership Changes, Quick and Easy

Scott Forsyth — Wed, 30 Jun 2004 20:02:00 GMT

Kenneth, thanks for the post. I'll get the script for stored procedures and add it here. I'll remove your post then too. David Penton will have something for this, I'm sure.