March 29, 2013 at 1:16 pm
I found a nifty but unsupported stored procedure that loops through all databases on your server and allows you do to things to each database. I would like it to just return a list of db_users for each database.
I tried executing as follows but I get a seperate result set for each loop:
execute sp_msforeachdb 'use[?]; SELECT name, type, type_desc
FROM sys.database_principals '
Can someone show how to get only one result set listing all databases with corresponding users, type, and type_desc in one result set?
March 29, 2013 at 1:25 pm
create a temp table, and instead of SELECTING, do INSERT INTO...SELECT:
CREATE TABLE [dbo].[#TMP] (
[DBNAME] NVARCHAR(256) NULL,
[NAME] SYSNAME NOT NULL,
[TYPE] CHAR(1) NOT NULL,
[TYPE_DESC] NVARCHAR(120) NULL);
execute sp_msforeachdb 'INSERT INTO #tmp SELECT ''?'' As DbName,name, type, type_desc FROM [?].sys.database_principals ';
SELECT * FROM #tmp;
Lowell
March 29, 2013 at 1:35 pm
Thank you Lowell. I still Still STILL must learn to utilize these temp tables and other tools! Thank you for showing me.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy