Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Inno
Starting Member
33 Posts |
 Posted - 2008-01-11 : 03:25:19
|
| I have a problem.I need to restore db ANNA into db BABB in order to create a database for testing. And so far I have no problem. However:The owner of each db has the same name as the db.After making the restore the owner of the tables, views and sp in database BABB is of course ANNA from the restore. I need to change theese back into the user BABB since I have an application that will try and access the db with this user.I cannot just change the login name, I need to replace the user that was lost in the restore.Can anyone help me? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-01-11 : 05:05:51
|
| Have a look at sp_changeobjectowner.Add the new login and user (and schema?), change the ownership for all objects then you can drop the user.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Inno
Starting Member
33 Posts |
Posted - 2008-01-11 : 05:13:17
|
| I have looked at it, but the problem is that I would have to specify each table, view and so on... |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-01-11 : 05:35:33
|
| select 'exec sp_changeobjectowner @objname=''' + table_name + ''', @newowner = ''BABB'''from information_schema.tableswhere table_schema = 'ANNA'Do the same for other objects==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Inno
Starting Member
33 Posts |
Posted - 2008-01-11 : 05:40:07
|
| Cool, how do you do it for stored procedures? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-01-11 : 05:44:03
|
| information_schema.viewsinformation_schema.routines(or you could use sysobjects instead)==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Restore db to different db and rename