January 13, 2015 at 7:36 am
DECLARE @SQLStatement VARCHAR(1000)
DECLARE @T_DBuser TABLE (DBName SYSNAME, UserName SYSNAME, AssociatedDBRole VARCHAR(256)) --sysname stores object names; is functionally the same as nvarchar(128) except that, by default, sysname is NOT NULL
SET @SQLStatement='
SELECT ''?'' AS DBName,dp.name AS UserName,USER_NAME(drm.role_principal_id) AS AssociatedDBRole
FROM ?.sys.database_principals dp
LEFT OUTER JOIN ?.sys.database_role_members drm
ON dp.principal_id=drm.member_principal_id
WHERE dp.sid NOT IN (0x01) AND dp.sid IS NOT NULL AND dp.type NOT IN (''C'') AND dp.is_fixed_role <> 1 AND dp.name NOT LIKE ''##%'' AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'')
ORDER BY DBName'
INSERT @T_DBuser
EXEC sp_MSforeachdb @SQLStatement --This system procedure creates a cursor, loops through all of your databases, and performs some action against each of them
SELECT * FROM @T_DBuser ORDER BY DBName
I have this script... How can I get the same results but not using the sp_MSforeachdb procedure maybe even not using dynamic coding.
Thanks,
Andrei
January 14, 2015 at 10:08 am
The dynamic SQL is going to be hard to get around unless you hard code all your USE DATABASE statements.
There are two ways of getting around the sp_MSforeachdb. The first is to hard code all your USE DATABASE statements, the second is to throw your database names in a temp table (or use SELECT DISTINCT name FROM sys.databases to populate your variable), using dynamic SQL for the USE DATABASE part of the script, then running the rest of the script as non-dynamic SQL.
Truth be told, you have the shortest script you can find for your needs. Anything else requires a lot more typing. But if you want to get away from both, then absolutely you should.
January 15, 2015 at 2:43 am
Thanks a lot for the reply.
Here is what I've end up with
DECLARE @SQL nvarchar(max)
if object_id('tempdb..#Result','U') IS not NULL
DROP TABLE #Result
CREATE TABLE #Result (DBName SYSNAME, UserName SYSNAME, AssociatedDBRole VARCHAR(256))
SELECT @SQL = coalesce(@SQL,'') + ' USE ' + QUOTENAME([Name]) + ';
INSERT INTO #Result
SELECT ' + quotename([Name],'''') + ' AS DbName
,dp.name AS UserName
,USER_NAME(drm.role_principal_id) AS AssociatedDBRole
FROM sys.database_principals dp
LEFT OUTER JOIN sys.database_role_members drm
ON dp.principal_id=drm.member_principal_id
WHERE dp.sid NOT IN (0x01) AND dp.sid IS NOT NULL AND dp.type NOT IN (''C'') AND dp.is_fixed_role <> 1 AND dp.name NOT LIKE ''##%''
' FROM sys.databases
WHERE database_id > 4
EXEC sp_executesql @SQL
select * from #Result order by [DbName]
Andrei
January 15, 2015 at 4:37 am
I'm glad you found something you could work with.
Happy coding. @=)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply