Help with SQL syntax to do the following in one command

The following code is a great idea (refer to Figure A below). Would it be possible to bundle this into one command that does all of these steps but also creates the temp table, dumps the output into a tab delimited text file (i.e., c:\dbexport.txt), then deletes the procedure and temp table (if these are even necessary ... actually i'd prefer this command to not use an sp and table if possible)?

FIGURE A:
(1) Create the following code as sp_GetPerms in the master database:
select
prin.name,
perms.state_desc,
db_name() as [DBName],
perms.permission_name,
case perms.major_id
when 0 then 'DATABASE'
else OBJECT_NAME(major_id)
end as [Object]
 from sys.database_permissions perms
join sys.database_principals prin on perms.grantee_principal_id=prin.principal_id
where prin.type in ('U','S')

(2) Then you could create a temp table that has the same structure as the above query and do....
insert into #temptable
exec sp_msforeachdb "use ?
go
exec sp_GetPerms"

select * from #temptable

This would return a single recordset with all of the user perms.
RexxSenior IT AuditorAsked:
Who is Participating?
 
dbaSQLCommented:
you can cursor through the databases with the SELECT, like this
use tempdb;

DECLARE @sql NVARCHAR(2000)
DECLARE @db_name sysname  

--Create table variable to hold query stats  
CREATE TABLE #results (name varchar(55),statedesc varchar(55),dbname varchar(55),permname varchar(55),objectname varchar(55))
--INSERT #results (name,statedesc,dbname,permname,objectname)

--Populate #results table
DECLARE db CURSOR FOR SELECT name FROM master..sysdatabases WHERE DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'  
OPEN db   
WHILE 1 = 1   
BEGIN
	FETCH NEXT FROM db INTO @db_name   
	IF @@FETCH_STATUS <> 0   
	BREAK   
	SET @sql = 'USE ' + QUOTENAME(@db_name) + ' select 
	prin.name,
	perms.state_desc,
	db_name() as [DBName],
	perms.permission_name,
	case perms.major_id when 0 then ''DATABASE''	
		else OBJECT_NAME(major_id)
	end as [Object]
from sys.database_permissions perms join sys.database_principals prin 
  on perms.grantee_principal_id=prin.principal_id
where prin.type in (''U'',''S'')
'
	INSERT INTO #results(name,statedesc,dbname,permname,objectname)   
	EXEC (@sql)   
END   
CLOSE db   
DEALLOCATE db   


select * from #results
DROP TABLE #results;

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.