sp_helprotect on all databases using sp_MSforeachdb [closed]

Question

Closed. This question does not meet Stack Overflow guidelines. It is not currently accepting answers.

Questions must demonstrate a minimal understanding of the problem being solved. Tell us what you've tried to do, why it didn't work, and how it should work. See also: Stack Overflow question checklist

Closed 12 years ago.

Improve this question

Anyone able to convert the following SQL query using sp_MSforeachdb? I am using SQL Server 2005 SP4. Thanks!

DECLARE @tmpResults TABLE
(Owner sysname, Object sysname, Grantee sysname, Grantor sysname,  ProtectType nvarchar(10), Action nvarchar(60), Column1 sysname)

DECLARE @DB_Grant TABLE
(DBName sysname, Owner sysname, Object sysname, Grantee sysname, Grantor sysname,  ProtectType nvarchar(10), Action nvarchar(60), Column1 sysname)

USE master
INSERT INTO @tmpResults EXEC sp_helprotect;
INSERT INTO @DB_Grant SELECT DB_Name(), t.* FROM @tmpResults t
DELETE @tmpResults

USE model
INSERT INTO @tmpResults EXEC sp_helprotect;
INSERT INTO @DB_Grant SELECT DB_Name(), t.* FROM @tmpResults t
DELETE @tmpResults

-- Repeat for all remaining DB

SELECT * FROM @DB_Grant
ORDER BY DBName, Grantee, Action

"Quel", apparently. But "Quelle" sounds besser, IMHO.

wildplasser
– wildplasser

2013-07-06 15:18:49 +00:00
Commented Jul 6, 2013 at 15:18 — wildplasser
– wildplasser, Commented Jul 6, 2013 at 15:18

steoleary · Accepted Answer · 2013-07-05 11:45:02Z

1

This should do it:

IF OBJECT_ID('tempdb..#dbGrant') IS NOT NULL
    DROP TABLE #dbGrant
IF OBJECT_ID('tempdb..#tempResults') IS NOT NULL
    DROP TABLE #tempResults

CREATE TABLE #dbGrant
(
[DBName] sysname, 
[Owner] sysname, 
[Object] sysname, 
[Grantee] sysname, 
[Grantor] sysname,  
[ProtectType] nvarchar(10), 
[Action] nvarchar(60), 
[Column] sysname
)

CREATE TABLE #tempResults
(
[Owner] sysname, 
[Object] sysname, 
[Grantee] sysname, 
[Grantor] sysname,  
[ProtectType] nvarchar(10), 
[Action] nvarchar(60), 
[Column1] sysname
)

EXEC sp_MSforeachdb 'IF ''?''  NOT IN (''tempDB'')
BEGIN
        INSERT INTO #tempResults EXEC sp_helprotect;
        INSERT INTO #dbGrant SELECT ''?'',t.* FROM #tempResults t
        TRUNCATE TABLE #tempResults
END'

SELECT * FROM #dbGrant
ORDER BY DBName, Grantee, Action

DROP TABLE #dbGrant,#tempResults

edited Jul 5, 2013 at 11:45

answered Jul 5, 2013 at 9:41

steoleary

9,3682 gold badges38 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

gcormier9 Over a year ago

I believe it is only missing 'USE [?]' after the 'BEGIN' statement.

steoleary Over a year ago

You don't need USE in this as the stored procedure handles that for you. If this has helped you, please consider accepting the answer.

gcormier9 Over a year ago

USE is required. I tested it and without USE, sp_helprotect is run multiple time on the same DB.

Collectives™ on Stack Overflow

sp_helprotect on all databases using sp_MSforeachdb [closed]

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related