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 |
|
akc42
Starting Member
6 Posts |
 Posted - 2011-05-01 : 04:48:08
|
| I am in the process of preparing the ground for live running as a switch over from Access back end to SQL Server back end for an Access Front End Application.I took my backup of the test database I have been developing on and restored it into what will become the new live database - not for the data, which I will migrate directly from the access .mdb file when we get to switch over, but to pickup all the stored procedures etc that I had developed.I have a login called a_design which is the owner of the databases. I also have logins called a_app, a_backup and a_batch each of which perform specific roles.I just discovered (when an overnight backup procedure failed - not catastrophic since I am only using the current time to check they are working) that the restore I did yesterday destroyed the connection between the database users and the logins.I understand from the microsoft web site that the stored procedure to recover these is now depreciated, and that I should use ALTER USER xxx WITH LOGIN = yyy to recreate the links. This I have done with a_app, a_backup and a_batch However, in the previous mapping login a_design was actually mapped to user dbo (since a_design created the database). When I do ALTER USER dbo WITH LOGIN = a_design (in Management Studio) I get an error message saying I cannot alter user dbo.Is there a way round this?I should add that I am logged in to Management Studio as "sa" |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-01 : 09:45:58
|
| try sp_changedbowneror alter authorization==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
Restoring Database - disconnecting logins gotcha