changing owners

So, today I got a database .BAK file, and had to restore it into my server, where it had never been before.  Having no documentation or information at all about the database, I went for it...

Pretty straightforward:

1) Create the database.

2) Restore over it from the .BAK file.

We don't need their old users or permissions, we just wanted to start working with the data.  Except we found that one user was owner of all the tables, views, and procedures.  That user of course had no login at the server level.

I created the login, but could find no way to associate an existing database user with a newly created server login.  Perhaps someone can show me the way?  There has to be a way....

Anyway, not being one to spend time learning when I need to get something done quickly, I looked for the answer for only a few minutes, then I used a tool that I've come to know and love: ProcBlaster from Lockwood Technologies.  So, the remainder of this post is to show you how to use this tool to fix this problem.

ProcBlaster is a simple tool to use, that allows you to generate code based on your database objects.  I guess it's intended use is to create procedures based on your tables, but you can do pretty much whatever you want.  In about 45 seconds, I created the script below, which fixed my problems in another 45 seconds.  Very nice :)

The ProcBlaster script:

dim i
for i = 1 to objpb.Database.Tables.Count
 objpb.Print "sp_changeobjectowner '" & objpb.Database.Tables(i).Owner & "." & objpb.Database.Tables(i).Name & "', 'dbo'" & vbcrlf
 objpb.Print "GO" & vbcrlf

for i = 1 to objpb.Database.Views.Count
 objpb.Print "sp_changeobjectowner '" & objpb.Database.Views(i).Owner & "." & objpb.Database.Views(i).Name & "', 'dbo'" & vbcrlf
 objpb.Print "GO" & vbcrlf

for i = 1 to objpb.Database.Procedures.Count
 objpb.Print "sp_changeobjectowner '" & objpb.Database.procedures(i).Owner & "." & objpb.Database.procedures(i).Name & "', 'dbo'" & vbcrlf
 objpb.Print "GO" & vbcrlf



The produced output:

sp_changeobjectowner 'oldownername.objectname', 'dbo'


I really wanted to learn and use CodeSmith to do this, since that's FREE, but as normal, I just didn't have the time.  If any of you already know CodeSmith, and can post an example of how to do this same job in it, I'd appreciate it!


Print | posted on Friday, November 07, 2003 7:13 AM


# re: changing owners

left by Damian at 11/7/2003 7:41 AM Gravatar

Do a search in the SQLTeam forums for sp_fixlogins. There is a handy script in there for this reason.

Codesmith rocks... because I love to evangelise it... give me 10 minutes and I'll whip up a template for you ...

# re: changing owners

left by Damian at 11/7/2003 7:47 AM Gravatar
Actually, after a better look..

You can write a cursor to loop over sysobjects and write dynamic sql and execute it.

# re: changing owners

left by Travis Laborde at 11/7/2003 8:02 AM Gravatar
I tend to shy away from sysobjects when possible, and besides... where's the fun and learning in that? :)

I thought it might be nice for people to see about the ProcBlaster tool, and even nicer if I could trick someone into teaching me some CodeSmith! *Hint!* It's been WAY more than 10 minutes now... :)

# re: changing owners

left by tara at 11/7/2003 3:26 PM Gravatar
Wouldn't sp_change_users_login fix the association? That's what I've always used.

# re: changing owners

left by Travis Laborde at 11/7/2003 3:32 PM Gravatar
Aha! I knew someone would post something and make me look silly! Thanks!

# re: changing owners

left by Tara at 11/7/2003 6:06 PM Gravatar
Check out Chad's script if you need to do this for multiple users.
Comments have been closed on this topic.