Sql-server – How to wipe all databases and users from a SQL Server database

sql serverwindows-server

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?

Best Answer

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