Nice script.
But I had to increase the size of a2 to 75 to avoid truncation errors on SQL 2005.
Here is an updated script. I changed the names of the fields of table #t for readability.
USE master
go
BEGIN
DECLARE @databasename VARCHAR(30)
DECLARE cur CURSOR
FOR SELECT
name
FROM
sysdatabases
CREATE TABLE #result
(
dbname VARCHAR(30)
,result VARCHAR(300))
OPEN cur
FETCH NEXT FROM cur INTO @databasename
WHILE(@@fetch_status = 0)
BEGIN
CREATE TABLE #t
(
[Owner] VARCHAR(50)
,[Object] VARCHAR(75)
,Grantee VARCHAR(50)
,Grantor VARCHAR(50)
,ProtectType VARCHAR(50)
,[Action] VARCHAR(50)
,[Column] VARCHAR(50))
INSERT INTO
#t
EXEC sp_helprotect @username = NULL
INSERT INTO
#result
SELECT
@databasename
,ProtectType + ' ' + [Action] + ' on [' + [Owner] + '].['
+ [Object] + ']'
+ CASE WHEN (PATINDEX('%All%', [Column]) = 0)
AND ([Column] <> '.') THEN ' (' + [Column] + ')'
ELSE ''
END + ' to [' + Grantee + ']'
FROM
#t
DROP TABLE #t
FETCH NEXT FROM cur INTO @databasename
END
SELECT
*
FROM
#result
ORDER BY
dbname
CLOSE cur
DEALLOCATE cur
DROP TABLE #result
END
go
Bill Soranno
MCP, MCTS, MCITP DBA
Database Administrator
Winona State University
Maxwell 143
"Quality, like Success, is a Journey, not a Destination" - William Soranno '92