Stupid question about sa
-
When someone asked me about SQL Server best practices, I gave a blanket statement that BuiltIn Administrators should be removed. So, they removed BuiltIn Administrators.
Little did I know they would be using BuiltIn Administrators to connect their application to the DB. And little did I know that they would have no other accounts which have 'sa' privileges. Lastly, little did I know that they would not know the 'sa' password (either that, or they did not select mixed mode authentication when they installed SQL Server).
Does anyone know of a way around this other than uninstalling and reinstalling SQL Server?
Thanks
-
The easiest way I can think of right now is to restore the master database from the day before the changes were made.
Hope this help!
Regards,
Bodhayan.
-
master probably cannot be restored by someone other than sa - no?
-
Maybe a reinstall for SQL Server and then using the then selected sa/password combo to restore the master database?
-
This was a Question Of The Day. You can resort to mixed mode by doing the following:
Stop SQL Service down,
set the value of HKLM\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode to 2 in the registry,
Restart SQL Service
Obviously, of no use if the sa password has been changed and forgotten...
-
If the solution of changing registry doesn't work, Restore the master database to another SQL Server, shutdown it, copy the master.mdf and mastlog.ldf to your original server.
-
Definiteiyl not a stupid question.
Using a second server or second instance is the best awy to go. It won't see your databases and so they will be suspect on the 2nd server and might still be so on the first server, but sp_resetstatus will fix that.
-
To the original post... Builtin\Administrators should not be removed but rather explicitly Denied. The service accounts that run sql server should be domain level and do not require NT SA permissioning to function. This allows separation of duties between NT SAs and SQL SAs.
-
Do you have another server beside this one? if so, you can copy from syslogins (using bcp) - the builtin administrator row from server A to server B's master. Builtin Administrator has the same sys id for all servers.
-
few things to try.....
rebuildm command line utility to rebuild the master and then attach all user databases - may have to probably create all login ids or restore from old backups.
restore the master database on another server and find out all the ids that have access to the server from the syslogins server and if you have passwords for those ids. now try to login using this id and fix the builtin\admin group.
restore the master database from the day before the changes were made on a test server (locate any previous backups before the change was made) - detach that database from the test server; shutdown the probalmatic sql server and replace it with the detached one.
-
Not sure if this is what you are wanting but the ffg script will re-create the Builtin/Administrators Login without reinstalling SQL Server.
-------------------------------------------------------------------
USE master
go
EXEC sp_grantlogin 'BUILTIN\Administrators'
go
EXEC sp_defaultdb 'BUILTIN\Administrators', 'master'
go
EXEC sp_defaultlanguage 'BUILTIN\Administrators', 'us_english'
go
EXEC sp_addsrvrolemember 'BUILTIN\Administrators', 'sysadmin'
go
IF EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname='BUILTIN\Administrators')
PRINT '<<< CREATED LOGIN BUILTIN\Administrators >>>'
ELSE
PRINT '<<< FAILED CREATING LOGIN BUILTIN\Administrators >>>'
go
-------------------------------------------------------------------
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply