Reinstalling SQL Server 2005

[StevenK]

We might be in a situation whereby we have to consider reinstalling SQL Server 2005. This has come about due to some issues with security that we can't seem to resolve (I've made postings on a couple of issue in this regards last week).

If we are to reinstall SQL Server 2005 - what steps do we need to take?

I'm assuming that it would be a wise move to back up any of the databases we use and then take them offline before uninstalling and reinstalling SQL Server 2005.
The databases can then be reattached and made use of this way.

Is this a sufficient enough plan?
Or do we need to do more than this?

Furthermore, given that we think we've currently got security issues (we can't make use of any of the sysadmin abilities - including adding user groups to be given permissions or view the SQL Server Agent any more) - will we re-introduce these when we re-connect the databases again?
[Putting us back to where we started from....]

Any help / suggestions would be greatly appreciated.

Thanks in advance,
Steve

 

[mrdenny]

Before you reinstall, stop the SQL Server and Move the user databases do another location. Then do an uninstall then a clean install.

After you have installed SQL Server you can then attach the user databases. You will need to manually create the logins again and sync them to the users within the databases.

Your lack of sysadmin problems will have gone away as that information is stored in the master database which will have been overwritten when you reinstalled.

In theory you can leave the data files in place while you do this, but it's safer not to.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[StevenK]

Thanks for that information.
Given the information is stored in the master database - is there any means to update this to correct our issues we're encountering with security and the likes?

Thanks again.
Steve

 

[mrdenny]

If no one has sysadmin rights into the database, there is no way to grant sysadmin rights within the database.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[ptheriault]

Steve,
You really shouldn't give out sysadmin admin rights to users or application logins. That is a very bad practice to get into. Did you delete the Built-in admin user? That login will make all the local server admins 'sa' in the database server. But only if you haven't deleted it or modified it. But Denny is correct, If you don't have a current 'sa' then you can't create one.

- Paul
- Experience is the name every one gives to their mistakes.

 

[StevenK]

The security issue came about due to a known issue with regards to early (pre-2003 I think) versions of Microsoft Excel not being able to make use of Microsoft Query against SQL Server 2005 databases.
This gave errors when queries were attempted to be run.

Reviewing the web - one tip was to allow user groups to have security admin rights - which I did - resolving the problem - and then took away again - and oddly the problem was still solved.

Since this addition / removal of the user group to the security admin group I've been seeing issues with gaining security access on the server.... :-(

Steve