June 29, 2020 at 11:05 pm
I need to run a query that will return all of the users for all of the databases on a SQL query. I am able to get all of the users for a single database, but I really need all of the users for all databases in a single output result. I thought this would be fairly simple to locate through a web search, but I was mistaken.
Help would be greatly appreciated.
Thanks,
Tom
June 30, 2020 at 2:23 pm
the users in a database are contained in the single database. There is no way (that I know of) to get all of the users in a database without using some form of dynamic SQL. There is the undocumented (and sometimes buggy) sp_msforeachdb stored procedure you could use or you could use a cursor and loop through all databases. But there is no built-in way to list all users in all databases.
Another way would be with a bunch of UNION ALLs... something like:
SELECT
[name]
, 'database' AS database
FROM[database].[sys].[sysusers]
WHERE[issqlrole] = 0
UNION ALL
SELECT
[name]
, 'database2' AS database
FROM[database2].[sys].[sysusers]
WHERE[issqlrole] = 0
...
and it should work. That filters out the SQL Roles, but take out the WHERE clauses if you want roles as well.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 1, 2020 at 2:56 pm
Thanks. That is helpful information to keep me from spinning my wheels. 🙂
July 1, 2020 at 4:30 pm
I found a solution that achieves the goal and thought I would share. The following script by Shiva Challa.
USE MASTER
GO
BEGIN
DECLARE @SQLVerNo INT;
SET @SQLVerNo = cast(substring(CAST(Serverproperty('ProductVersion') AS VARCHAR(50)) ,0,charindex('.',CAST(Serverproperty('ProductVersion') AS VARCHAR(50)) ,0)) as int);
IF @SQLVerNo >= 9
IF EXISTS (SELECT TOP 1 *
FROM Tempdb.sys.objects (nolock)
WHERE name LIKE '#TUser%')
DROP TABLE #TUser
ELSE
IF @SQLVerNo = 8
BEGIN
IF EXISTS (SELECT TOP 1 *
FROM Tempdb.dbo.sysobjects (nolock)
WHERE name LIKE '#TUser%')
DROP TABLE #TUser
END
CREATE TABLE #TUser (
ServerName varchar(256),
DBName SYSNAME,
[Name] SYSNAME,
GroupName SYSNAME NULL,
LoginName SYSNAME NULL,
default_database_name SYSNAME NULL,
default_schema_name VARCHAR(256) NULL,
Principal_id INT,
[sid] VARBINARY(85))
IF @SQLVerNo = 8
BEGIN
INSERT INTO #TUser
EXEC sp_MSForEachdb
'
SELECT
@@SERVERNAME,
''?'' as DBName,
u.name As UserName,
CASE WHEN (r.uid IS NULL) THEN ''public'' ELSE r.name END AS GroupName,
l.name AS LoginName,
NULL AS Default_db_Name,
NULL as default_Schema_name,
u.uid,
u.sid
FROM [?].dbo.sysUsers u
LEFT JOIN ([?].dbo.sysMembers m
JOIN [?].dbo.sysUsers r
ON m.groupuid = r.uid)
ON m.memberuid = u.uid
LEFT JOIN dbo.sysLogins l
ON u.sid = l.sid
WHERE u.islogin = 1 OR u.isntname = 1 OR u.isntgroup = 1
/*and u.name like ''tester''*/
ORDER BY u.name
'
END
ELSE
IF @SQLVerNo >= 9
BEGIN
INSERT INTO #TUser
EXEC sp_MSForEachdb
'
SELECT
@@SERVERNAME,
''?'',
u.name,
CASE WHEN (r.principal_id IS NULL) THEN ''public'' ELSE r.name END GroupName,
l.name LoginName,
l.default_database_name,
u.default_schema_name,
u.principal_id,
u.sid
FROM [?].sys.database_principals u
LEFT JOIN ([?].sys.database_role_members m
JOIN [?].sys.database_principals r
ON m.role_principal_id = r.principal_id)
ON m.member_principal_id = u.principal_id
LEFT JOIN [?].sys.server_principals l
ON u.sid = l.sid
WHERE u.TYPE <> ''R''
/*and u.name like ''tester''*/
order by u.name
'
END
SELECT *
FROM #TUser
ORDER BY DBName,
[name],
GroupName
DROP TABLE #TUser
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply