August 5, 2008 at 12:41 pm
I would like to know if there is a quick way to enable/disable logins in SQL 2005. I have a warm server that while warm I would like all users disabled but when failed over to, I'd like a quick way to enable them all.
Does anyone know if an xp exists or if there is any custom code on the site for anyone who may have done this already?
TIA,
Christine
Aurora
August 5, 2008 at 12:59 pm
Use a script with ALTER LOGIN loginname DISABLE for each login and another script with ALTER LOGIN loginname ENABLE for each login. This works for both SQL Server logins and Windows Logins.
Greg
August 5, 2008 at 1:25 pm
Here you go.
Good way is to deny and grant permission to logins.
Create a two SQL JOB
In one JOB called this SP by issue parameter DENY if denying
other job called this SP by issue parameter GRANT if Granting
-- =============================================
-- Author:Balbir Singh
-- Create date: July 11, 2008
-- Description:[MNT_SP_ENABLE_DISABLE_LOGINS]
--
-- Modified
-- [MNT_SP_ENABLE_DISABLE_LOGINS] 'DENY'
-- =============================================
ALTER PROC [dbo].[MNT_SP_ENABLE_DISABLE_LOGINS]
@FLAG VARCHAR(10)
AS
DECLARE @SQLstr VARCHAR(2000)
DECLARE @LoginName VARCHAR(500)
-- CURSOR FOR LOGINS
DECLARE curLogin CURSOR FOR
SELECT [NAME] FROM SYS.SYSLOGINS
WHERE NAME NOT LIKE('##%') AND
NAME NOT IN
('sa','NT AUTHORITY\SYSTEM','BUILTIN\Administrators')
OPEN curLogin
FETCH NEXT FROM curLogin INTO @LoginName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQLstr = 'USE MASTER '+@FLAG +' CONNECT SQL TO ['+@LoginName+'] '
--print @SQLstr
EXEC (@SQLstr)
FETCH NEXT FROM curLogin INTO @LoginName
END
CLOSE curLogin
DEALLOCATE curLogin
SET NOCOUNT OFF
August 5, 2008 at 1:54 pm
Awesome, that's what I needed...
Thanks all!
Aurora
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply