January 14, 2008 at 4:47 pm
This one is better as it does not use the depreciated sys.logins but uses the new DMVs.
EXEC master..sp_MSForeachdb '
USE [?]
DECLARE @username AS VARCHAR(25), @Counter INT, @NbrLogins INT
SET @Counter = 1
SET @NbrLogins = (
SELECT COUNT(*)
FROM sys.server_principals
WHERE Type IN (''S'',''U'',''G'',''C'',''K'')
AND name NOT LIKE ''%##%''
AND name NOT IN(''BUILTIN\Administrators'',
''sa'', ''NT AUTHORITY\SYSTEM'')
)
SELECT ROW_NUMBER() OVER (ORDER BY name) AS [ID], name
INTO #tmp_logins
FROM sys.server_principals
WHERE Type IN (''S'',''U'',''G'',''C'',''K'')
AND name NOT LIKE ''%##%''
AND name NOT IN(''BUILTIN\Administrators'', ''sa'', ''NT AUTHORITY\SYSTEM'')
ORDER BY name
IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''
BEGIN
WHILE @Counter <= @NbrLogins
BEGIN
SET @username = (SELECT name FROM #tmp_logins WHERE @Counter = ID)
IF EXISTS(SELECT UID FROM SYSUSERS WHERE name = @username)
BEGIN
EXEC sp_addrolemember N''db_denydatawriter'', @username
END
SET @Counter = @Counter + 1
END
END
'
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply