Easily find this page again using the shortlink: https://bct.azrcrv.co.uk/2000000053
Table Type: System
Table Name: Access Control
Database Table Name: Access Control
Due to how Dynamics BC tables and columns are named, square brackets need to be wrapped around the table name and column names. Below are example queries showing how Access Control can be queried.
Show/hide an example select of all columns
SELECT
*
FROM
[Access Control]Show/hide an example select of all columns by name
SELECT
[User Security ID]
,[Role ID]
,[Company Name]
,[Scope]
,[App ID]
,[$systemId]
,[$systemCreatedAt]
,[$systemCreatedBy]
,[$systemModifiedAt]
,[$systemModifiedBy]
FROM
[Access Control]Show/hide an example select of all columns including those from all joined tables
SELECT
*
FROM
[Access Control] AS [Access Control]
LEFT JOIN
[User] AS [User]
ON
[Access Control].[User Security ID] = [User].[User Security ID]
LEFT JOIN
[Company] AS [Company]
ON
[Access Control].[Company Name] = [Company].[Name]
LEFT JOIN
[User] AS [User]
ON
[Access Control].[SystemCreatedBy] = [User].[User Security ID]
LEFT JOIN
[User] AS [User]
ON
[Access Control].[SystemModifiedBy] = [User].[User Security ID]Show/hide columns in Access Control table
| Field No. | Field Name | Field Caption | Class | Type | Length | SQL Column Name | SQL DataType |
|---|---|---|---|---|---|---|---|
| 1 | User Security ID   | User Security ID | Normal | GUID | 16 | User Security ID | uniqueidentifier |
Key to join to the User table.Show/hide example querySELECT
[Access Control].[User Security ID]
,[User].[User Security ID]
FROM
[Access Control] AS [Access Control]
LEFT JOIN
[User] AS [User]
ON
[Access Control].[User Security ID] = [User].[User Security ID] | |||||||
| 2 | Role ID | Role ID | Normal | Code | 20 | Role ID | nvarchar(40) |
| 3 | Company Name | Company Name | Normal | Text | 30 | Company Name | nvarchar(60) |
Key to join to the Company table.Show/hide example querySELECT
[Access Control].[Company Name]
,[Company].[Name]
FROM
[Access Control] AS [Access Control]
LEFT JOIN
[Company] AS [Company]
ON
[Access Control].[Company Name] = [Company].[Name] | |||||||
| 5 | User Name | User Name | FlowField | Code | 50 | ||
| 7 | Role Name | Role Name | FlowField | Text | 30 | ||
| 8 | Scope | Scope | Normal | Option | 4 | Scope | int |
Available options are:
| |||||||
| 9 | App ID | App ID | Normal | GUID | 16 | App ID | uniqueidentifier |
| 10 | App Name | App Name | FlowField | Text | 250 | ||
| 2000000000 | $systemId | System ID | Normal | GUID | 16 | $systemId | uniqueidentifier |
| 2000000001 | SystemCreatedAt | Created At | Normal | DateTime | 8 | $systemCreatedAt | datetime |
| 2000000002 | SystemCreatedBy | Created By | Normal | GUID | 16 | $systemCreatedBy | uniqueidentifier |
Key to join to the User table.Show/hide example querySELECT
[Access Control].[$systemCreatedBy]
,[User].[User Security ID]
FROM
[Access Control] AS [Access Control]
LEFT JOIN
[User] AS [User]
ON
[Access Control].[$systemCreatedBy] = [User].[User Security ID] | |||||||
| 2000000003 | SystemModifiedAt | Modified At | Normal | DateTime | 8 | $systemModifiedAt | datetime |
| 2000000004 | SystemModifiedBy | Modified By | Normal | GUID | 16 | $systemModifiedBy | uniqueidentifier |
Key to join to the User table.Show/hide example querySELECT
[Access Control].[$systemModifiedBy]
,[User].[User Security ID]
FROM
[Access Control] AS [Access Control]
LEFT JOIN
[User] AS [User]
ON
[Access Control].[$systemModifiedBy] = [User].[User Security ID] | |||||||