April 2, 2015 at 12:40 pm
OS: Windows: 8.0
SQL Server: SQL Server 2012 Express SP2
Environment:
•SSMS SQL Server 2012
•Commands executed from the nSpekUser SQL Server account which has db_creator permission but not sysadmin
•Open a query window
•Execute the following command:
restore database [065266F14D20] from disk = 'C:\Users\rforte\AppData\Local\TempSpekSqlServerBackups\065266F14D20.bak' with
norecovery, CREDENTIAL,
move 'nSpekServer' to 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESSSP2\MSSQL\DATA\065266F14D20.mdf',
move 'nSpekServer_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESSSP2\MSSQL\DATA\065266F14D20.ldf'
restore log [065266F14D20] from disk = 'C:\Users\rforte\AppData\Local\TempSpekSqlServerBackups\065266F14D20.bak' with recovery
•I receive the following messages:
Processed 3072 pages for database '065266F14D20', file 'nSpekServer' on file 1.
Processed 4 pages for database '065266F14D20', file 'nSpekServer_log' on file 1.
RESTORE DATABASE successfully processed 3076 pages in 0.581 seconds (41.350 MB/sec).
Processed 0 pages for database '065266F14D20', file 'nSpekServer' on file 1.
Processed 4 pages for database '065266F14D20', file 'nSpekServer_log' on file 1.
RESTORE LOG successfully processed 4 pages in 0.024 seconds (1.037 MB/sec).
Indicating that all went well. And indeed, I can see the new restored database.
•I then execute:
use [065266F14D20]
and I get the following error:
The server principal "nSpekUser" is not able to access the database "065266F14D20" under the current security context.
•The source database comes from another computer.
•When I look at the User Mappings for nSpekUser from the sa account, I find that there is no dbo associated with the created database.
•If I set nSpekUser as dbo on the new database, then all is fine.
•When I grant sysadmin to the nSpekUser account, it all works well.
The problem is that this sequence is normally executed from an installation procedure at a customer’s place having no account with sysadmin permission and no access to the sa account. Thus, the installation cannot proceed normally.
Question:
How may I restore a database with db_creator permissions and have the dbo set to the account from which I restored the database.
April 2, 2015 at 2:02 pm
Immediately after every restore of that db, run this command:
EXEC sp_change_users_login 'UPDATE_ONE', 'nSpekUser', 'nSpekUser'
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 2, 2015 at 2:07 pm
When I entre this command, it tells me that "User does not have permisisons to perform this action".
I am starting to wonder if you have to be sysadmin to restore a database whose backup was taken on another computer. Because this is precisely what I am trying to do.
April 2, 2015 at 2:15 pm
You don't have to be a full sysadmin, necessarily, but you do need the proper authority/permissions.
The dbcreator role should handle the restore.
You'd need to be db_owner to issue the sp_change_users_login command.
You can avoid having to issue that command if you change either of the existing logins to match the SID of the login on the other server. You would have to:
1) drop the existing login
2) re-create that login, explicitly specifying the same SID as the other server for that login
3) go thru every db and re-sync the user and the login in any db in which it currently exists. This is a one-time thing. You would never have to do this again.
From then one, you could restore that db from one server to the other without having to issue any re-sync commands, and the user and the login will automatically re-sync themselves and activate all existing permissions on that db.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 2, 2015 at 2:28 pm
Well,
The account I perform my restore from is dbcreator and the restore operation itself works well. It is just that it leaves the database with no dbo, so it may be accessed only through a sysadmin account. I am not trying to restore an account previously found in the original database; I am just trying to access my database from the account that performed the restore operation. As I said previously, if I set the nSpekUser account as db_owner through the sa account after the restore, I can access the database just fine. But, in real life, I do not have access to a sysadmin account. The funny thing is that, in the database properties, it indicates that the database owner is nSpekUser but that acocunt simply cannot access it.
April 2, 2015 at 2:35 pm
real.forte 18467 (4/2/2015)
Well,The account I perform my restore from is dbcreator and the restore operation itself works well. It is just that it leaves the database with no dbo, so it may be accessed only through a sysadmin account. I am not trying to restore an account previously found in the original database; I am just trying to access my database from the account that performed the restore operation. As I said previously, if I set the nSpekUser account as db_owner through the sa account after the restore, I can access the database just fine. But, in real life, I do not have access to a sysadmin account. The funny thing is that, in the database properties, it indicates that the database owner is nSpekUser but that acocunt simply cannot access it.
Because after the restore the 'nSpekUser' user in the db is not linked to the 'nSpekUser' login on the server. Until they are linked, the local db permissions won't be available.
You need someone with full authority to:
1) run the command above after every restore
2) create a proc that runs under such permission and give you permission to run the proc
3) syncs the SIDs so that from then on the user and login will automatically match
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 2, 2015 at 3:02 pm
So, what you are saying is that, when I restore a database having an SID that does not match any account in the server I restore it to, I have to fix this through a sysadmin account, is that right?
April 2, 2015 at 3:05 pm
real.forte 18467 (4/2/2015)
So, what you are saying is that, when I restore a database having an SID that does not match any account in the server I restore it to, I have to fix this through a sysadmin account, is that right?
Sysadmin or a db_owner on that db, that is correct.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 3, 2015 at 12:28 pm
Thank you very much for the information. Here is what I ended up doing:
1. I fetched the SID of the dbo on the original server
2. Provided I have securityadmin permission:
a. If the user did exist on the target server I dropped it (I can do this safely since it is dedicated to this application)
b. I recreated it giving it the remote server SID
3. I restored the database.
In the case where I do not have securityadmin permission, I send a message asking the user to create the account and I give him/her the specs of the account to create including the SID.
Thank you once again.
April 3, 2015 at 1:04 pm
real.forte 18467 (4/3/2015)
Thank you very much for the information. Here is what I ended up doing:1. I fetched the SID of the dbo on the original server
2. Provided I have securityadmin permission:
a. If the user did exist on the target server I dropped it (I can do this safely since it is dedicated to this application)
b. I recreated it giving it the remote server SID
3. I restored the database.
In the case where I do not have securityadmin permission, I send a message asking the user to create the account and I give him/her the specs of the account to create including the SID.
Thank you once again.
You're very welcome, but just be sure ...
About 2a:
Do you mean "login" rather than user? It is the SID of the login that must match the original server; just changing the SID of the user within the db won't help it sync up.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 3, 2015 at 1:07 pm
Right. I am sorry. That was a 'typo'
April 3, 2015 at 1:11 pm
Sorry, I'm a DBA, so I'm highly technical on those terms ;-).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply