Sql-server – How to prevent users from overwriting databases when restoring

Assuming you can get access to the box (if you need to recover access, see the link Kin already pointed out), you need to:

• Drop all user databases. Please run the PRINT first and make sure you're not dropping anything you may regret. You may want to take backups of all databases first just in case.

  DECLARE @sql NVARCHAR(MAX) = N'';
  
  SELECT @sql += N'
    DROP DATABASE ' + QUOTENAME(name)
    + N';' 
  FROM sys.databases
  WHERE name NOT IN (N'master',N'tempdb',N'model',N'msdb');
  
  PRINT @sql;
  -- EXEC master.sys.sp_executesql @sql;
  

• Drop all non-system logins. Again, please run the PRINT command first, and look the list over very carefully. I have a filter there to prevent dropping yourself if you are logged in via Windows Authentication, but you may want to add additional specific filters in the event you are logged in as sa or similar and don't want to drop your Windows login.

  DECLARE @sql NVARCHAR(MAX) = N'';
  
  SELECT @sql+= N'
    DROP LOGIN ' + QUOTENAME(name) + ';' 
  FROM sys.server_principals 
  WHERE name <> N'sa'
  AND name NOT LIKE N'##%'
  AND name NOT LIKE N'NT [AS]%'
  AND [type] IN ('R', 'S', 'U', 'G')
  AND principal_id > 256
  AND name <> SUSER_SNAME(); -- don't drop yourself!
  
  PRINT @sql;
  -- EXEC master.sys.sp_executesql @sql;
  

• Correct the default database for any logins you keep. You may find you can lock yourself out if your default database no longer exists. So, set your login to default to master or tempdb:

  ALTER LOGIN [DOMAIN\Login] WITH DEFAULT_DATABASE = tempdb;
  

• Correct any jobs/maintenance plans that reference old databases. You'll need to look through the list manually and identify anything that touches these databases, otherwise they'll start failing. You can get a starting list with this, but this won't necessarily be a complete list - it won't tell you if the job uses ad hoc SQL that references a database with three-part naming or calls a stored procedure that does so. Thankfully you won't have to worry about synonyms (unless some evil person put synonyms in system databases).

  SELECT j.name 
  FROM msdb.dbo.sysjobs AS j
  WHERE EXISTS 
  (
    SELECT 1 
      FROM msdb.dbo.sysjobsteps AS js
      WHERE js.job_id = j.job_id
      AND js.database_name NOT IN (N'msdb',N'master',N'tempdb',N'model')
  );
  

  In fact, you may want to run this step first, so that you can clean up all the jobs before they start failing. I'd also validate that all the jobs that remain still have valid owners. You'll have to correct any of these jobs after you delete any logins:

  SELECT j.name FROM msdb.dbo.sysjobs AS j
  WHERE NOT EXISTS
  (
    SELECT 1 
      FROM sys.server_principals AS p
      WHERE p.sid = j.owner_sid
  );
  

The above issue which you are facing is commonly known as the case for Orphan users:

What commonly happens with SQL authenticated logins and database users on a restore is that the SIDS will be out of sync, thus breaking the relationship. This relationship must be repaired before you can connect to the database using that login, because in the eyes of SQL Server those principals are no longer connected.

On how to fix this you can refer Fixing Orphaned Users

Or as an alternative you can also make use of SP_help_revlogin

1) Run the sp_help_revlogins against the MASTER on SOURCE SERVER, and get the script of "creating logins".

2) During the restoring the backup to the DESTINATION SERVER, you can run the "creating logins" script against the MASTER database on the DESTINATION SERVER.

3) After the restoration DONE, please EXEC sp_change_users_login 'REPORT' against the RESTORED database.