Thursday, May 31, 2007 at 1:32 pm by Brady Wilson
Microsoft SQL Server TSQL change object ownership script
This script will change the ownership of user objects in the target database to the specified user.
This situation often occurs when importing a database from another SQL server where a user account existed that does not, and will not exist on the new SQL server.
Run this script to update user object ownership before clearing the old/incorrect user from the database.
This script could be turned into a stored procedure as well and accept the new username as a passed parameter.
There are multiple ways to do this some of which are probably more glamorous – but hey, it works.
——————————————————————————
/*
This script will change the ownership of user objects in the target database to the specified user.
This situation often occurs when importing a database from another SQL server where a user account existed that does not, and will not exist on the new SQL server.
Run this script to update user object ownership before clearing the old/incorrect user from the database.
*/
SET NOCOUNT ON
/* Set the new owner name here in place of the ‘dbo’ user */
DECLARE @New_Owner_Name Sysname
SET @New_Owner_Name = ‘dbo’
DECLARE @Object_ID Int, @Object_Name Sysname, @Owner_ID Int, @Owner_Name Sysname, @Owner_Object_Name Sysname
DECLARE CUR_ReplaceObjectOwner CURSOR FOR
/* Run this select query by itself to see the set of user objects you are working with */
SELECT so.id AS “Object ID”, so.name AS “Object Name”, so.uid AS “Owner ID”, su.name AS “Owner Name”, ‘[' + su.name + '].’ + ‘[' + so.name + ']‘ AS “Owner + Object Name”
FROM sysobjects so, sysusers su
WHERE so.uid = su.uid
AND so.xtype IN (’U',’P',’FN’,'V’)
AND so.name NOT LIKE ‘%dt_%’
AND so.name NOT IN (’syssegments’, ’sysconstraints’)
ORDER BY xtype
/* Get the results from the query and load the first row into the cursor */
OPEN CUR_ReplaceObjectOwner
FETCH NEXT FROM CUR_ReplaceObjectOwner
INTO @Object_ID, @Object_Name, @Owner_ID, @Owner_Name, @Owner_Object_Name
WHILE @@FETCH_STATUS = 0
BEGIN
IF @New_Owner_Name != @Owner_Name
/* Make the object owner change here */
EXEC sp_changeobjectowner @Owner_Object_Name, @New_Owner_Name
/* Load the next row into the cursor */
FETCH NEXT FROM CUR_ReplaceObjectOwner
INTO @Object_ID, @Object_Name, @Owner_ID, @Owner_Name, @Owner_Object_Name
END
/* Clean up */
CLOSE CUR_ReplaceObjectOwner
DEALLOCATE CUR_ReplaceObjectOwner
SET NOCOUNT OFF
GO
——————————————————————————
Article Archives:
Virtualized for Non-Profit
Opus Interactive has virtualized 22 of their servers in a effort to upgrade internal systems. Instead of trying to sell the servers, or reprovision them, Opus Interactive decided the best thing to do is to donate them to Omni Media Networks Inc. The donated servers were needed in an effort to expand Omni Media Networks Internet outreach programs. Opus Interactive is very [...]
July 1st, 2008
If a tree gets planted in the forest will anyone hear?
Opus Interactive has joined with Arbor Day Foundation in their mission to “…inspire people to plant, nurture and celebrate trees.” Arbor Day Foundation is the largest nonprofit tree-planting organization, with nearly one million members and averages over 12 million trees planted each year.
So with that, the question becomes with almost 12 million trees each year, why [...]
June 16th, 2008
Protecting the minds and hearts of our children with OpenDNS
There is no argument that there is a lot of crap on the Internet. And I am not referring to poorly designed web sites, time wasting games and an overabundance of news about absolutely everything. The deep, dark alleys of the Internet (and sometimes not all that far removed) are filled with pornography, crime, drugs, [...]
June 9th, 2008
Interop Las Vegas 08 Photo Journal
Interop Las Vegas 08 . The adventures over the course of 5 days. Below you will see somethings you wish you didn’t and other you are glad to see. Enjoy.
Arrived at the MGM Grand Hotel Checked in Room #4136. Look at that view.
Well what would Vegas be like without a little gambling? Eric at his first slot [...]
May 22nd, 2008
Smokeping on FreeBSD 7
This write-up assumes a working copy of FreeBSD 7.0. It was built using 7.0-RELEASE. It should work on FreeBSD 6.x-STABLE and future versions of FreeBSD 7. The package versions listed were current as of this writing but may have been updated by the time someone uses this howto.
This is a basic setup of Smokeping. There [...]
April 24th, 2008
Installing MySQL4 and MySQL5 on a single FreeBSD 6.2 Server.
This write-up makes the following assumptions:
Working copy of FreeBSD 6.2.
Build: 6.2-RELEASE.
Which should work on 6.2-STABLE and 7.0 as well.
Please let me know if you run into typos or other technical issues when implementing this.
Download the latest binaries from mysql.com.
At write-up time this was:
mysql-5.0.45-freebsd6.0-i386.tar.gz
mysql-standard-4.1.22-unknown-freebsd6.0-i386.tar.gz
Install MySQL 4.
Uncompress the binary source.
# cd /usr/local
# gunzip < [...]
March 11th, 2008
Article Comments: