Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

List all Usernames, Roles for all the databases. Expand / Collapse
Author
Message
Posted Thursday, October 2, 2008 2:44 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 1, 2013 7:40 AM
Points: 145, Visits: 336
Thanks Cathy !!

Do you mind if I add this to the online script?


Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help
Post #579956
Posted Wednesday, October 8, 2008 6:57 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 16, 2012 7:34 PM
Points: 59, Visits: 375
shiva challa (10/2/2008)
Thanks Cathy !!

Do you mind if I add this to the online script?


Not at all. Please do. :)

Cathy
Post #582507
Posted Wednesday, October 8, 2008 7:57 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 1, 2013 7:40 AM
Points: 145, Visits: 336
Here is the updated script.
I have also updated the online script, Just waiting for it to be published.
/**
Script: list all Usernames, Roles for all the databases.
Author: Shiva Challa (http://challa.info)
and the database Roles that the user belongs to in all the databases.
Also, you can use this script to get the roles of one user in all the databases.

Directions of Use:
For All Users list: You can directly run this script in SQL Server Management studio
For a specific user:
        1. Find this code and u.name like ''tester''
        2. Uncomment the code
        3. Replace the Name ''tester'' with the username you want to search on.

Resultset:
        DBName: Database name that the user exists in.
        Name: user name.
        GroupName: Group/Database Role that the user is a part of.
        LoginName: Actual login name, if this is null, Name is used to connect.
        default_database_name
        default_schema_name    
        principal_id
        sid

Change History:
08/26/2008 Shiva Challa - Removed the misc characters from the "Select of EXEC sp_MSForEachdb" statement.
09/02/2008 Cathy Greenselder - Convert to SQL2000
(default_database_name not in SQL2K)
(default_schema_name not in SQL2K)
(principal_id not in SQL2K)
(uid is in SQL2K
10/08/2008 Shiva Challa - Added Cathy's script to the original script with an IF logic to make it work for both SQL 2000 and SQL 2005.
**/

USE MASTER
GO

BEGIN
IF EXISTS (
SELECT TOP 1 *
FROM Tempdb.sys.objects (nolock)
WHERE name LIKE '#TUser%')
DROP TABLE #TUser

CREATE TABLE #tuser (
DBName VARCHAR(50),
[Name] SYSNAME,
GroupName SYSNAME NULL,
LoginName SYSNAME NULL,
default_database_name VARCHAR(50) NULL,
default_schema_name VARCHAR(256) NULL,
Principal_id INT,
sid VARBINARY(85))

IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '8'
INSERT INTO #TUser
EXEC sp_MSForEachdb
'
SELECT
''?'' 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.memberuid = 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
'

ELSE IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '9'
INSERT INTO #TUser
EXEC sp_MSForEachdb '
SELECT
''?'',
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
'

SELECT *
FROM #TUser
ORDER BY DBName,
[name],
GroupName

DROP TABLE #TUser
END




Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help
Post #582565
Posted Wednesday, October 8, 2008 11:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 1:17 PM
Points: 33, Visits: 221
CAGreensfelder (10/2/2008)
Adapted for SQL 2000

/**
Script: list all Usernames, Roles for all the databases.
Author: Shiva Challa (http://challa.info)
and the database Roles that the user belongs to in all the databases.
Also, you can use this script to get the roles of one user in all the databases.

Directions of Use:
For All Users list: You can directly run this script in SQL Server Management studio
For a specific user:
        1. Find this code and u.name like ''tester''
        2. Uncomment the code
        3. Replace the Name ''tester'' with the username you want to search on.

Resultset:
        DBName: Database name that the user exists in.
        Name: user name.
        GroupName: Group/Database Role that the user is a part of.
        LoginName: Actual login name, if this is null, Name is used to connect.
        default_database_name
        default_schema_name    
        principal_id
        sid

Change History:
8/26/2008 Removed the misc characters from the "Select of EXEC sp_MSForEachdb" statement.


9/2/2008 Cathy Greenselder - Convert to SQL2000
        (default_database_name not in SQL2K)
        (default_schema_name    not in SQL2K)
        (principal_id not in SQL2K)
uid is in SQL2K

**/

USE MASTER
GO

BEGIN

IF EXISTS (SELECT * FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].#TUser')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE #TUser

CREATE TABLE #tuser (
DBName VARCHAR(50),
UserName SYSNAME,
GroupName SYSNAME NULL,
LoginName SYSNAME NULL,
uid INT,
sid VARBINARY(85))

INSERT INTO #TUser
EXEC sp_MSForEachdb
'
SELECT ''?'' as DBName,
u.name As UserName,
CASE
WHEN (r.uid IS NULL) THEN ''public''
ELSE r.name
END AS GroupName,
l.name AS LoginName,
u.uid,
u.sid
FROM ?.dbo.sysUsers u
LEFT JOIN (?.dbo.sysMembers m
JOIN ?.dbo.sysUsers r
ON m.memberuid = 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
'

SELECT *
FROM #TUser
ORDER BY DBName,
UserName,
GroupName

DROP TABLE #TUser
END





After I run the script the UserName and the GroupName are the same.
Shouldn't the GroupName reflect which group the UserName is associated with....if username: xyz has dbo rights to a database shouldn't dbo show in the GroupName.

Please advise.
Thanks
Post #582809
Posted Wednesday, October 8, 2008 11:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 1:17 PM
Points: 33, Visits: 221
shiva challa (10/8/2008)
Here is the updated script.
I have also updated the online script, Just waiting for it to be published.
/**
Script: list all Usernames, Roles for all the databases.
Author: Shiva Challa (http://challa.info)
and the database Roles that the user belongs to in all the databases.
Also, you can use this script to get the roles of one user in all the databases.

Directions of Use:
For All Users list: You can directly run this script in SQL Server Management studio
For a specific user:
        1. Find this code and u.name like ''tester''
        2. Uncomment the code
        3. Replace the Name ''tester'' with the username you want to search on.

Resultset:
        DBName: Database name that the user exists in.
        Name: user name.
        GroupName: Group/Database Role that the user is a part of.
        LoginName: Actual login name, if this is null, Name is used to connect.
        default_database_name
        default_schema_name    
        principal_id
        sid

Change History:
08/26/2008 Shiva Challa - Removed the misc characters from the "Select of EXEC sp_MSForEachdb" statement.
09/02/2008 Cathy Greenselder - Convert to SQL2000
(default_database_name not in SQL2K)
(default_schema_name not in SQL2K)
(principal_id not in SQL2K)
(uid is in SQL2K
10/08/2008 Shiva Challa - Added Cathy's script to the original script with an IF logic to make it work for both SQL 2000 and SQL 2005.
**/

USE MASTER
GO

BEGIN

IF EXISTS (SELECT TOP 1 *
FROM Tempdb.sys.objects (nolock)
WHERE name LIKE '#TUser%')
DROP TABLE #TUser

CREATE TABLE #tuser (
DBName VARCHAR(50),
[Name] SYSNAME,
GroupName SYSNAME NULL,
LoginName SYSNAME NULL,
default_database_name VARCHAR(50) NULL,
default_schema_name VARCHAR(256) NULL,
Principal_id INT,
sid VARBINARY(85))

IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '8'
INSERT INTO #TUser
EXEC sp_MSForEachdb
'
SELECT
''?'' 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.memberuid = 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
'

ELSE IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '9'
INSERT INTO #TUser
EXEC sp_MSForEachdb '
SELECT
''?'',
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
'

SELECT *
FROM #TUser
ORDER BY DBName,
[name],
GroupName

DROP TABLE #TUser
END




Getting this error when I run this script in sql 2000:

Server: Msg 208, Level 16, State 1, Line 4
Invalid object name 'Tempdb.sys.objects'.

Please advise.
Thanks
Post #582810
Posted Wednesday, October 8, 2008 12:50 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 1, 2013 7:40 AM
Points: 145, Visits: 336
KDASQL (10/8/2008)
Getting this error when I run this script in sql 2000:
Server: Msg 208, Level 16, State 1, Line 4
Invalid object name 'Tempdb.sys.objects'.


This is happening because there is no sys.objects in SQLServer 2000

Replace the first IF statement with this, it wil work:
 
IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '9'
IF EXISTS (
SELECT TOP 1 *
FROM Tempdb.sys.objects (nolock)
WHERE name LIKE '#TUser%')
DROP TABLE #TUser
ELSE IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '9'
IF EXISTS (
SELECT TOP 1 *
FROM Tempdb.dbo.sysobjects (nolock)
WHERE name LIKE '#TUser%')
DROP TABLE #TUser

Apologise for this over look. I dont have a SQL 2000 instance for testing.


Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help
Post #582854
Posted Wednesday, October 8, 2008 1:01 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 1, 2013 7:40 AM
Points: 145, Visits: 336
Here is the most updated script, With added code to use SysObjects in 2000 instead of sys.objects


/**
Script: list all Usernames, Roles for all the databases.
Author: Shiva Challa (http://challa.info)
and the database Roles that the user belongs to in all the databases.
Also, you can use this script to get the roles of one user in all the databases.

Directions of Use:
For All Users list: You can directly run this script in SQL Server Management studio
For a specific user:
        1. Find this code and u.name like ''tester''
        2. Uncomment the code
        3. Replace the Name ''tester'' with the username you want to search on.

Resultset:
        DBName: Database name that the user exists in.
        Name: user name.
        GroupName: Group/Database Role that the user is a part of.
        LoginName: Actual login name, if this is null, Name is used to connect.
        default_database_name
        default_schema_name    
        principal_id
        sid

Change History:
08/26/2008 Shiva Challa - Removed the misc characters from the "Select of EXEC sp_MSForEachdb" statement.
09/02/2008 Cathy Greenselder - Convert to SQL2000
(default_database_name not in SQL2K)
(default_schema_name not in SQL2K)
(principal_id not in SQL2K)
(uid is in SQL2K
10/08/2008 Shiva Challa - Added Cathy's script to the original script with an IF logic to make it work for both SQL 2000 and SQL 2005.
- Added code to use SysObjects in 2000 instead of sys.objects

**/

USE MASTER
GO

BEGIN

IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '9'
IF EXISTS (
SELECT TOP 1 *
FROM Tempdb.sys.objects (nolock)
WHERE name LIKE '#TUser%')
DROP TABLE #TUser
ELSE IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '8
IF EXISTS (
SELECT TOP 1 *
FROM Tempdb.dbo.sysobjects (nolock)
WHERE name LIKE '#TUser%')
DROP TABLE #TUser

CREATE TABLE #tuser (
DBName VARCHAR(50),
[Name] SYSNAME,
GroupName SYSNAME NULL,
LoginName SYSNAME NULL,
default_database_name VARCHAR(50) NULL,
default_schema_name VARCHAR(256) NULL,
Principal_id INT,
sid VARBINARY(85))

IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '8'
INSERT INTO #TUser
EXEC sp_MSForEachdb
'
SELECT
''?'' 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.memberuid = 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
'

ELSE IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '9'
INSERT INTO #TUser
EXEC sp_MSForEachdb '
SELECT
''?'',
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
'

SELECT *
FROM #TUser
ORDER BY DBName,
[name],
GroupName

DROP TABLE #TUser
END



Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help
Post #582864
Posted Wednesday, October 8, 2008 3:31 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 1:17 PM
Points: 33, Visits: 221
shiva challa (10/8/2008)
Here is the most updated script, With added code to use SysObjects in 2000 instead of sys.objects


/**
Script: list all Usernames, Roles for all the databases.
Author: Shiva Challa (http://challa.info)
and the database Roles that the user belongs to in all the databases.
Also, you can use this script to get the roles of one user in all the databases.

Directions of Use:
For All Users list: You can directly run this script in SQL Server Management studio
For a specific user:
        1. Find this code and u.name like ''tester''
        2. Uncomment the code
        3. Replace the Name ''tester'' with the username you want to search on.

Resultset:
        DBName: Database name that the user exists in.
        Name: user name.
        GroupName: Group/Database Role that the user is a part of.
        LoginName: Actual login name, if this is null, Name is used to connect.
        default_database_name
        default_schema_name    
        principal_id
        sid

Change History:
08/26/2008 Shiva Challa - Removed the misc characters from the "Select of EXEC sp_MSForEachdb" statement.
09/02/2008 Cathy Greenselder - Convert to SQL2000
(default_database_name not in SQL2K)
(default_schema_name not in SQL2K)
(principal_id not in SQL2K)
(uid is in SQL2K
10/08/2008 Shiva Challa - Added Cathy's script to the original script with an IF logic to make it work for both SQL 2000 and SQL 2005.
- Added code to use SysObjects in 2000 instead of sys.objects

**/

USE MASTER
GO

BEGIN

IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '9'
IF EXISTS (
SELECT TOP 1 *
FROM Tempdb.sys.objects (nolock)
WHERE name LIKE '#TUser%')
DROP TABLE #TUser
ELSE IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '8
IF EXISTS (
SELECT TOP 1 *
FROM Tempdb.dbo.sysobjects (nolock)
WHERE name LIKE '#TUser%')
DROP TABLE #TUser

CREATE TABLE #tuser (
DBName VARCHAR(50),
[Name] SYSNAME,
GroupName SYSNAME NULL,
LoginName SYSNAME NULL,
default_database_name VARCHAR(50) NULL,
default_schema_name VARCHAR(256) NULL,
Principal_id INT,
sid VARBINARY(85))

IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '8'
INSERT INTO #TUser
EXEC sp_MSForEachdb
'
SELECT
''?'' 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.memberuid = 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
'

ELSE IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '9'
INSERT INTO #TUser
EXEC sp_MSForEachdb '
SELECT
''?'',
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
'

SELECT *
FROM #TUser
ORDER BY DBName,
[name],
GroupName

DROP TABLE #TUser
END





After I run the script the UserName and the GroupName are the same.
Shouldn't the GroupName reflect which group the UserName is associated with....if username: xyz has dbo rights to a database shouldn't dbo show in the GroupName.

Please advise.
Thanks
Post #582964
Posted Thursday, October 9, 2008 7:35 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 16, 2012 7:34 PM
Points: 59, Visits: 375
No idea. I'm not real sure what the groups are supposed to look like. Someone who is actually a 2000 dba will have to look at it. I'm a developer and my database gets restored from a copy from production. Most of the users from the prod database don't have logins on my dev server so my results are mostly garbage.

BTW you can get the default db on SQL2K with:

l.dbname AS Default_db_Name,

On the first go, I just nulled it out because I hadn't looked at it closely enough to find the 2000 equivalent of the 2005 field. I don't think 2000 has schemas.
Post #583303
Posted Thursday, October 9, 2008 7:56 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 16, 2012 7:34 PM
Points: 59, Visits: 375
Ah ha! The center part of the 2000 join should be:

FROM HISAppt.dbo.sysUsers u
LEFT JOIN (HISAppt.dbo.sysMembers m
JOIN HISAppt.dbo.sysUsers r
ON m.groupuid = r.uid) -- groupuid not memberuid
ON m.memberuid = u.uid
LEFT JOIN dbo.sysLogins l
ON u.sid = l.sid


That fixes the group thing.
Post #583322
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse