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
next

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
next

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
next

%}

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!