I wrote this code the other day that disables all user logins in SQL Server 2000. If you have the need to be the only one on the server in SQL for doing maint, troubleshooting, etc., then you'll find this useful. I've given you both the adhoc version and the SP version. Hope someone can use it... because even sa can be kept out of the server this way.
When you use this, don't forget to put yourself in the exclude list in that NOT IN clause. Otherwise you'll lock everyone out and you'll need to restore master to get everything back.
And since this hits the system table directly, I'm not responsible for any damage you do to your system with this code. Use it at your own risk.
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH override
GO
--Disable users.
UPDATE sysxlogins
SET xstatus = 1|xstatus
WHERE name NOT IN ('user1', 'user2')
--Enable users.
UPDATE sysxlogins
SET xstatus = 1^xstatus
WHERE name NOT IN ('user1', 'user2')
sp_configure 'allow updates', 0
GO
RECONFIGURE WITH override
GO
sp_helptext syslogins
------------------------Turn into SP-----------------------------------
--
--Create Procedure dbo.spDisableAllUsers
--
--@Users varchar(3)
--
--as
--
--/*
--Disables all user accts on the server.
--Author: Sean McCown
--Date: 6/25/2007
--
--*/
--
--DECLARE @sql nvarchar(1000)
--
--SET @sql = 'sp_configure ''allow updates'', 1; reconfigure with override;'
--
--EXEC (@sql)
--
--
--IF @Users = 'OFF'
-- BEGIN
--
-- update master..sysxlogins
-- set xstatus = 1|xstatus
-- where name NOT in ('user1', 'user2')
--
-- END
--
--IF @Users = 'ON'
-- BEGIN
-- update master..sysxlogins
-- set xstatus = 1^xstatus
-- where name NOT in ('user1', 'user2')
--
--END
--
--SET @sql = 'sp_configure ''allow updates'', 0; reconfigure with override;'
--
--EXEC (@sql)
OK, so it's worth mentioning that this code doesn't work in Yukon. Not only can you disable accts in Yukon with 'alter login', you'd have to be able to touch the system tables directly, which is just too much trouble. The one thing that you can't do in Yukon, that you can do with the code above is disable windows groups. BOL says that's by design, and I suppose I can see that, but it would be really handy to be able to throw your server into a mode where only certain admins can connect, etc. Now, to get around this problem, all you have to do is make sure that no matter what you do, you don't give anyone dbo in any of your DBs. Then you can just throw all of your DBs into dbo only mode before you start your script. Another method would be to take your DBs offline if you don't need them. So if you're performing maint on several DBs one at a time, then disable all DBs except the one you're working on, and then disable it when you're done, and enable the next one in dbo only mode. This way, you can't control users connecting to the box, but they won't be able to do anything.
Now, all this has come about because MS decided to not allow us to disable windows groups. Now, I'm not saying that's a good thing or a bad thing... I'm just saying it's the reason we have to do it this way.
Posted by Sean McCown on June 26, 2007 08:02 AM
