Note: Please review the Knowledgebase article How to report on User and Security Group rights or permissions if you need to print a report on the details of Security Groups. There is currently no way in SQL to view the granular level of security as displayed in the report as most of it is done in code and partially from complex SQL cursor queries. Hence the security report may not reconcile completely with the results from the SQL query.
It should be noted with respect to the "rights" values, the following may be helpful:

Rights of 15 = Access = Yes at Main Level (not inherited rights)
Rights of 14 = Access = No at Sub Level

Display all faculty that have an online ID and belong to one or more groups in The Education Edge

SELECT
      r.LASTNAME + ', ' + r.FIRSTNAME as 'Name',
      r.EA7RECORDSID,
      r.USERDEFINEDID, 
      r.ONLINEUSERID,
      sg.NAME as 'GroupName'
FROM dbo.EA7RECORDS r
JOIN dbo.EA7FACULTY f on f.EA7RECORDSID = r.EA7RECORDSID
JOIN dbo.EA7USERFACULTY uf on uf.EA7FACULTYID = f.EA7FACULTYID
JOIN dbo.SECURITYUSERS7 su on su.USERSID = uf.USERSID
JOIN dbo.SECURITYGROUPS7 sg on su.SECURITYGROUPS7ID = sg.SECURITYGROUPS7ID
ORDER BY r.LASTNAME ASC
Note: If the faculty member belongs to more than one group there is more than one entry in the returns for the above SQL statement.  Also important to note that if the user is not a faculty member or does not belong to a group in EE, they will not appear on the list.
Display user name with corresponding Security Groups

SELECT USERS.NAME, SECURITYGROUPS7.DESCRIPTION, SECURITYMODULES7.DESCRIPTION, SECURITYMODULES7.RIGHTS,
SECURITYMODULES7_1.DESCRIPTION, SECURITYMODULES7_1.RIGHTS

FROM SECURITYGROUPS7
INNER JOIN SECURITYMODULES7 ON SECURITYGROUPS7.SECURITYGROUPS7ID = SECURITYMODULES7.SECURITYGROUPS7ID
INNER JOIN SECURITYUSERS7 ON SECURITYGROUPS7.SECURITYGROUPS7ID = SECURITYUSERS7.SECURITYGROUPS7ID
INNER JOIN USERS ON SECURITYUSERS7.USERSID = USERS.USERSID
INNER JOIN SECURITYMODULES7 AS SECURITYMODULES7_1 ON SECURITYMODULES7.SECURITYMODULES7ID = SECURITYMODULES7_1.SUPERMODULEID

--WHERE SECURITYGROUPS7.DESCRIPTION='Data Entry - regular users'
ORDER BY USERS.NAME


Display user name with rights information checkmark

SELECT USERS.NAME, SECURITYGROUPS7.DESCRIPTION, SECURITYMODULES7.DESCRIPTION, SECURITYMODULES7.RIGHTS,
SECURITYMODULES7_1.DESCRIPTION, SECURITYMODULES7.SETTINGNUMBER, --SECURITYMODULES7.MODULENUMBER,
(SELECT Checkbox =
CASE SECURITYMODULES7_1.RIGHTS
WHEN 15 THEN 'YES'
ELSE 'NO'
END) as CHECKMARK

FROM SECURITYGROUPS7
LEFT OUTER JOIN SECURITYMODULES7 ON SECURITYGROUPS7.SECURITYGROUPS7ID = SECURITYMODULES7.SECURITYGROUPS7ID
LEFT OUTER JOIN SECURITYUSERS7 ON SECURITYGROUPS7.SECURITYGROUPS7ID = SECURITYUSERS7.SECURITYGROUPS7ID
LEFT OUTER JOIN USERS ON SECURITYUSERS7.USERSID = USERS.USERSID
LEFT OUTER JOIN SECURITYMODULES7 AS SECURITYMODULES7_1 ON SECURITYMODULES7.SECURITYMODULES7ID = SECURITYMODULES7_1.SUPERMODULEID

WHERE USERS.NAME is not NULL
--AND SECURITYMODULES7.SUPERMODULEID = 290 -- Record Types
ORDER BY USERS.NAME


RIGHTS values are as follows:

1 - (0001) - View
2 - (0010) - Add
4 - (0100) - Edit
8 - (1000) - Delete

So a RIGHTS value of 13 would be Binary 1101 which would mean

8 - Delete - On (checked in Security Module)
4 - Edit - On
2 - Add - Off
1 - View - On

The following chart shows all possible values for RIGHTS and what switches would be turned on.

0 = off (unchecked)
1 = on (checked)

 
Value Delete Edit Add View
10001
20010
30011
40100
50101
60110
70111
81000
91001
101010
111011
121100
131101
141110
151111

 

Disclaimer: Blackbaud provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes you are familiar with Structured Query Language and the tools used to create and modify SQL statements and Crystal Reports. Blackbaud Customer Support may help explain the functionality of a particular procedure, but we will not modify, or assist you with modifying, these examples to provide additional functionality.