Stored Procedure
Sec_GetPermissionsOnEntity.sp: Lists the permissions where the user or user group has a security association that includes the permissions on the defined entity.
Inputs
|
Name |
Description |
Required |
|---|---|---|
|
userId |
The user ID for the user. You can retrieve the user ID from the UMUsers table: SELECT id FROM UMUsers WHERE login = 'user_name'. |
Yes |
|
entityType |
The type of entity. For example, set this to 28 to return all of the client groups a user is associated with. The ID for each type of entity is available in the APP_Entity table. |
Yes |
|
entityId |
The entity ID for the entity. You can retrieve the entity ID from the appropriate entity table. For example, SELECT id FROM APP_ClientGroup WHERE name = 'G1'. |
Yes |
|
inheritFromChildren |
The option to return permissions from child entities. Permissions from parent entities are always returned. Valid values are:
|
Yes |
Output
The list of permission IDs where the user or user group has a security association that includes the permissions on the defined entity.
Note: If a user or user group is associated with the parent of an entity with permissions P1, P2, P3 and is associated with the entity with permissions P4 and P5, permissions P1 through P5 are returned. The permissions from child entities are returned if inheritFromChildren is set to 1.
Sample Query
This query lists permissions that user "u1" has on client group "G1":
--sec_getPermissionsOnEntity.sp
--If you want to get list of permissions that u1 has on a client group G1.
DECLARE @userId INT = (SELECT id FROM UMUsers WHERE login = 'u1')
DECLARE @entityType INT = 28 --Client group entity. You can get it from APP_Entity table.
DECLARE @entityID INT = (SELECT id FROM APP_ClientGroup WHERE name = 'G1')
IF OBJECT_ID('tempdb.dbo.#getPermissionsExample') IS NOT NULL
DROP TABLE #getPermissionsExample
CREATE TABLE #getPermissionsExample (permissionID INT)
EXEC sec_getPermissionsOnEntity'#getPermissionsExample', @userId, @entityTYpe, @entityID
SELECT * FROM #getPermissionsExample