4

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?

7

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
    );
    
| improve this answer | |
1

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.

| improve this answer | |
  • Why not install another instance of SQL Server -> I don't have the original media – Aaron Bertrand Oct 27 '14 at 20:32
  • @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 ? – Kin Shah Oct 27 '14 at 20:48
  • 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. – Aaron Bertrand Oct 27 '14 at 20:57
  • I do not have the original media or access to the MSDN copy. – Michael Brennan-White Oct 29 '14 at 12:26
  • 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. – Michael Brennan-White Oct 29 '14 at 12:32
0

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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.