If I restore a database which does not already exist then the database's owner will be me.
Probably not a good idea that I own all the databases just because I restored them, initially - e.g. when migrating databases to a new server. If my login gets removed then ... trouble I expect! plus I automatically get "db_owner" permission too, which may not be good either.
Is there a way to restore "AS sa"? or do is the only way to do:
ALTER AUTHORIZATION ON DATABASE:: MyDatabaseName to sa
but then am I going to have to figure out if I need to remove myself from "db_owner" (I might already, legitimately, have that on that database), or does just a RESTORE not add me to that role?
I thought about pre-creating the database ... but that's going to be owned by ME too
There is a connect issue which Microsoft has closed "as by design", and they give the rationale for this behavior. There is a workaround on that page; perhaps that will work for you?
I could, but for us SA is reserved, not actually used by anyone. I'd have to go get the password out of the safe ...
Which happens to be the one I have adopted but its one-more-thing-to-remember whenever I restore a database
I think MS's replies are a bit of a cop-out:
There are a number of reasons behing the current behavior:
1) In the general case, we cannot assume that the user who owned the database which was
backed up even exists on the instance that it's being restored to.
2) We need to ensure that the user currently performing the restore has the necessary priveleges
to apply log backups to the newly restored database.
3) We ensure that the user currently doing the restore has full privileges on the database, and can
thus change ownership to whatever is appropriate on the target system. If the database is
read-only, it can briefly be set to R/W for the modification, and then set back.
#1 is true, but a RESTORE AS xxx would allow choosing a new owner (which would be required to exist). Currently I can restore as me then delete my Server Login I expect? Seems to me as broad as it is long on that account: Fail to restore with non-existent UserID from old machine vs. restore with any-old-login which then is inappropriate
#2 could be solved by issuing the "AS xxx" option in combination with the "WITH RECOVERY" command
#3 "briefly" setting a READ_ONLY database to READ_WRITE is probably Gross Misconduct in some places! let alone the consequences of forgetting/failing to re-set it back to READ_ONLY.
OK, I'll stick to ALTER AUTHORIZATION ON DATABASE for now, and I'll stick that in my Restore Script Snippets file
EXECUTE AS LOGIN = 'sa';
RESTORE DATABASE MyDatabaseName
FROM DISK = 'X:\Path\MyDatabaseName_20150823_040100_Full.BAK'
WITH
REPLACE, -- (Restored to a name that didn't already exist)
NORECOVERY,
STATS = 10,
MOVE 'MyDatabaseName_data' TO 'E:\Path\MyDatabaseName.mdf',
MOVE 'MyDatabaseName_log' TO 'F:\Path\MyDatabaseName.ldf';
RESTORE DATABASE MyDatabaseName WITH RECOVERY;
SELECT [Owner name] = SUSER_SNAME(owner_sid)
, [Database name] = [name]
FROM sys.databases AS D
WHERE D.name = 'MyDatabaseName'
I've re-read it and I don't read it like that - there is a sense that you could log on as SA, and then RESTORE (which I was not about to adopt as a strategy!!) , but I didn't read using EXECUTE AS from it. I wish I had done though!
What would be nice is if I could determine, FROM a backup file, WHO the original owner was and then, if that user exists on the target server, dynamically set the EXECUTE AS
All our backups are made under our control, so I could include the Owner in the Comment within the Backup, and then parse that out during the Restore.
(I don't restore using an SProc, but I do use an SProc to prepare the RESTORE command. I can give it as much / little info as I have; for example, if I give it a parameter for the database name it will list for me all available backup files, along with suitable EXEC SProc syntax which then includes parameters for database name and filename, so I can then cut & paste one of the listed filenames into the SProc command. Once I have Database Name and File Name I get an Sproc syntax for a full set of commands - which includes target folder (system default is offered) for MDF/LDF. Finally, once the SProc has "sufficient" parameters it spews out the full RESTORE syntax, including options for a DIFF and TLog backups, commented out bits to set the DB back to READ_WRITE, the code to rescue Orphaned Logons (if Login doesn't exist it includes a Create for that, and a commented out command for a Drop User in case, as part of restoring the DB, I want to get rid of stale users)
Long story short, I could include the EXECUTE AS in the generated code, based on something parsed from the Backup Comment.
Are we looking at the same thing? This is what I am seeing under the workarounds tab (although he should be using suser_sname rather than user_name see if he has really changed the execution context, and his comments are not in sync with the code)
Posted by vjsekars on 1/19/2011 at 3:54 AM Try this.. Executing user might require sysadmin previlage..
SELECT user_name(); -- Shows execution context is set to SqlUser1.
EXECUTE AS login='SA';
SELECT user_name(); -- Shows execution context is set to SqlUser2, the caller of the module.
RESTORE DATABASE [DBA23] FROM DISK = N'd:\DBMaint_Full.bak'
REVERT;
SELECT user_name(); -- Shows execution context is set to SqlUser1.
GO
BTW, I reserve the right to disown everything I say until late into Monday morning.
but its one-more-thing-to-remember whenever I restore a database
... let me re-check it
