Locked out - please help

Question

Locked out - please help

devman

SSC Rookie

Points: 34
More actions
October 8, 2003 at 5:12 am

#160817

Hi
Following some advice on the web about securing our SQL 2000 installation, we have managed to get 'locked out' of SQL.
We switched over to Windows Authentication and removed the BUILTIN\Administrators group. Problem now is that we cannot add new databases, users etc. Thankfully the existing ones are still all working fine.
We cannot login as 'sa' anymore, and things are becoming desparate as we need to create a new DB pretty quick.
Can any one help? Do you need more info?
TIA

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Andy Warren SSC Guru Points: 119902 More actions · Answer 1

Do you have any user assigned to the sysadmin role? Maybe someone else on the team or you network admin? Worst case you can run RebuildM to generate new master/msdb, then attach all the db's. Not a step to be done lightly or without a backup.

Andy

http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

What about putting BUILTIN\Administrators back in, assign proper permissions and take the time to think about what went wrong?

Which OS version and service pack!!! are you on?

Just curious, but under NT4 with anything less than SP6a removing this login can cause severe problems.

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

SQLBill SSC Guru Points: 51440 More actions · Answer 3

BTW-SA account ONLY works with SQL Authentication.

-SQLBill

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 4

If you do remember 'sa' password, you can change the registry key to make SQL Server work in mixed authentication mode.

Regedt32.exe --> hkey_local_machine --> software --> microsoft --> MSSQLSERVER --> MSSQLSERVER --> Change 'LoginMode' to 2.

devman SSC Rookie Points: 34 More actions · Answer 5

quote:
What about putting BUILTIN\Administrators back in, assign proper permissions and take the time to think about what went wrong?
Which OS version and service pack!!! are you on?

Windows 2000 SP4

SQL 2000 SP3

Partial success: we have installed another instance of SQL alongside and have managed to export/import between the two to salvage some databases.

However, there are two that have no users associated with them at all.

We cannot add back the BUILTIN\Administrators as they were removed completely.

I understand what when wrong - we did things in the wrong order leaving no users left to administer the databases!

devman SSC Rookie Points: 34 More actions · Answer 6

quote:
Do you have any user assigned to the sysadmin role? Maybe someone else on the team or you network admin?

The sysadmin role has no users 🙁

hoo-t SSChampion Points: 12713 More actions · Answer 7

Do you have a backup of the msdb database prior to removing builtin/admin? If so, you can restore msdb and your builtin/admin will be back.

Failing that, to get your remaining two databases moved to the new instance, stop the old instance, and simply attach the databases to the new instance with sp_attach_db.

Steve Phelps

SQL Server DBA

American Fidelity Group

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 8

You mean master... Syslogins is in master.

Did no one remember the sa password? If so, set the registry key to 2 as suggested and you can log in as sa. If the sa password was never set it is blank...

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/

K. Brian Kelley
@kbriankelley

devman SSC Rookie Points: 34 More actions · Answer 9

We tried the Reg change and logging in as sa, but it appears that the sa password is long forgotton as it was never really used since install.

Will try the sp_attach_db.

Thanks for your input folks