How can I wipe all databases and users from a SQL Server database?

Question

Okay, here is the story.

I have a Windows 7 Ultimate box with a SQL Server 2008 R2 Standard installation that I inherited from a person who left the company long before I started at my company. No documentation about the users has been left.

I don't need any of users other than having the ability to set a sa password and don't need any of the databases currently on the box.

The only reason I can't remove SQL Server from the box is because I don't have the original media in this office and would have trouble getting the media from the other office. I need the standard install because one of the databases I want to load is 70GB in size (they stored images in tables).

Is there a way to just start fresh?

Answer 1

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
  );
  

• Michael Brennan-White: Unfortunately I can't get logged into the box.

Answer 2

I don't need any of users other than having the ability to set an sa password and don't need any of the databases currently on the box.

There are ways that you can use to reset sa password or refer to Aaron's article on MSSQLTips. For dropping all the databases, you can just use

select 'DROP DATABASE '+ name + char(10) + 'go'
from sys.databases where database_id > 4

The only reason I can't remove sql from the box is because I don't have the original media in this office and would have trouble getting the media from the other office. I need the standard install because one of the databases I want to load is 70GB in size (they stored images in tables).

Why not install another instance of SQL Server e.g. a named instance called INSTANCE2 or something similar that suits your needs.

BTW, there is no Windows 2007 version.

• Aaron Bertrand: Why not install another instance of SQL Server -> I don't have the original media
• Kin Shah: @AaronBertrand Agreed, but its still somewhat unclear as OP is telling I need the standard install because one of the databases I want to load. I assume starting fresh means clean up everything on the server - logins, all databases, etc. OP has not mentioned what edition and version he is using ?
• Aaron Bertrand: I was only explaining why the OP can't just install another instance - they don't have the disc and presumably can't get it easily or download it from MSDN/Volume Licensing. So instead they want to strip the existing instance down.
• Michael Brennan-White: I do not have the original media or access to the MSDN copy.
• Michael Brennan-White: I do not have the original media or access to the MSDN copy. @AaronBertrand I tried using the instructions from your well written article about using PsExec. I am able to login to SSMS but though I am logged in as NT AUTHORITY\SYSTEM the Object Explorer does not display any databases OR have any security options in either the explorer on the menu options.

Answer 3

You can use this Cursor for Dropping All DBs

DECLARE @DBName VARCHAR (64)
DECLARE @SQL VARCHAR (255)
DECLARE DROPDB CURSOR FOR 
SELECT name FROM sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb','distribution','ReportServer','ReportServerTempDB')
OPEN DROPDB
FETCH next FROM DROPDB INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
 SET @SQL = 'DROP DATABASE ' + @DBName
 PRINT @SQL
 EXEC @SQL
 FETCH next FROM DROPDB INTO @DBName
END
CLOSE DROPDB
DEALLOCATE DROPDB