How to find SQL-Server Database Users & Roles:-
DECLARE
@db_name SYSNAME,
@sql VARCHAR(1000),
@databaseNM varchar(50)
set @databaseNM='JDE_CRP'
DECLARE db_cursor CURSOR FOR SELECT Name FROM sys.databases where Name=@databaseNM
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @db_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @sql =
'SELECT
''' + @db_name + ''' AS [Database],
USER_NAME(role_principal_id) AS [Role],
USER_NAME(member_principal_id) AS [User]
FROM
' + @db_name + '.sys.database_role_members'
EXEC(@sql)
FETCH NEXT FROM db_cursor INTO @db_name
END
CLOSE db_cursor
DEALLOCATE db_cursor
