How to get list of users across projects including their groups

  • Questions
  • How to get list of users across projects including their groups
Question ID: 104561
3
0

I know that the Project and Domain information is stored in a seperate database than the projects.
Is there a way in SQL Server to get a list of the users with their assigned groups across all the projects?

Marked as spam
Posted by (Questions: 5, Answers: 1)
Asked on August 3, 2012 6:13 pm
25 views
Answers (1)
4
Private answer

I'm not a SQL Expert, but If you are using SQL Server try this...
It creates a dynamic SQL Statement and then runs the statement. (See Pictures Below)

**SQL STATEMENT**

--Dynamic SQL to List the Users by their Domain/Project/Group
DECLARE @err int
DECLARE @DB_Name varchar(100)
DECLARE @Command nvarchar(max)

--Declare Cursor Dynamic SQL
DECLARE @qcsiteadmin_db varchar(32)
DECLARE @dbown varchar(32)
SET @dbown = 'td'
SET @qcsiteadmin_db = 'qcsiteadmin_db'
DECLARE @DB_CURSOR_SQL nvarchar(max)
SET @DB_CURSOR_SQL = 'DECLARE database_cursor CURSOR FOR ' + 'SELECT DISTINCT DB_NAME FROM ' + @qcsiteadmin_db + '.' + @dbown + '.' + 'USERS_PROJECTS UP
INNER JOIN ' + @qcsiteadmin_db + '.' + @dbown + '.' + 'USERS U ON UP.USER_ID=U.USER_ID
INNER JOIN ' + @qcsiteadmin_db + '.' + @dbown + '.' + 'PROJECTS P ON UP.PROJECT_ID=P.PROJECT_ID'
EXEC sp_executesql @DB_CURSOR_SQL

--Process Rows
OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @DB_Name

SET @Command = ''
WHILE @@FETCH_STATUS = 0
BEGIN
IF db_id(@DB_Name) is not null
BEGIN
IF @Command <> '' SET @command = @Command +' UNION '
SET @Command = @Command + 'SELECT P.DB_NAME, P.DOMAIN_NAME, P.PROJECT_NAME, US_USERNAME, GR_GROUP_NAME FROM ' +
@DB_Name + '.' + @dbown + '.USERS U, '+ @DB_Name + '.' +
@dbown + '.GROUPS G, ' + @qcsiteadmin_db + '.' +
@dbown + '.PROJECTS P WHERE SUBSTRING(US_GROUP, GR_GROUP_ID+1, 1) = 1
AND U.US_USERNAME <> ''_system_user_''
AND P.DB_NAME = ' + '''' + @DB_Name + '''' + CHAR(13)
END
FETCH NEXT FROM database_cursor INTO @DB_Name
END

BEGIN TRY
EXEC sp_executesql @Command
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMessage
END CATCH

CLOSE database_cursor
DEALLOCATE database_cursor

**GENERATED DYNAMIC SQL STATEMENT**

![alt text][1]

**RESULTS**

![alt text][2]

[1]: /upfiles/SQL_STATEMENT_1_1.jpg
[2]: /upfiles/SQL_STATEMENT_RESULTS_1.jpg

Marked as spam
Posted by (Questions: 0, Answers: 613)
Answered on August 3, 2012 6:48 pm
WOW, works like a charm thank you. Yet another reason to use SQL Server over Oracle!
( at August 3, 2012 6:50 pm)
Tried in my enviornment works great!
( at August 3, 2012 6:56 pm)
Works in versions 10 and 11
( at August 3, 2012 6:58 pm)