Auth

Users & Roles

Getting Users

All AD User AD Group SQL User SQL User without Login

SELECT
    [name]
    , [type_desc]
    , [type]
FROM [sys].[database_principals]

SELECT
    [name]
FROM [sys].[database_principals]
WHERE
    [type] = 'E'
    AND [name] = 'username@mail.com'
;

SELECT
    [name]
FROM [sys].[database_principals]
WHERE
    [type] = 'X'
    AND [name] = 'username@mail.com'
;

SELECT
    [name]
FROM [sys].[database_principals]
WHERE
    [type] = 'S'
    AND [name] = 'username@mail.com'
;

SELECT
    [name]
FROM [sys].[database_principals]
WHERE
    [type] = 'S'
    AND [name] = 'username@mail.com'
;

---

Create User

AD User AD Group SQL User SQL User without Login

USE [<database-name>];
CREATE USER [username@mail.com] FROM EXTERNAL PROVIDER
GO

USE [<database-name>];
CREATE USER [group-name] FROM LOGIN [group-name];
GO

USE [master];
CREATE LOGIN <username> WITH PASSWORD = 'P@ssW0rd'
GO

USE [<database-name>];
CREATE USER <username> FOR LOGIN <username>;
GO

USE [<database-name>];
CREATE USER <username> WITHOUT LOGIN;
GRANT IMPERSONATE ON USER::<username> TO [<another-username>];
GO

EXECUTE AS USER = '<username>';
GO
...
REVERT;
GO

Warning

Azure Synapse Serverless SQL Pool does not support for create this user type.

Getting User Example:

Note

If you want to drop user, you would use:

DROP USER [username@mail.com]
GO

If this user have login, you would use:

DROP LOGIN [username@mail.com]
GO

Generate drop statement with multi-users:

SELECT CONCAT('DROP USER [', [name], '];')  AS remove_user
FROM
    [sys].[database_principals]
WHERE
    [type] = 'E'
    AND LOWER([name]) IN ('username@mail.com', ...)
;

---

Getting Relations

List relate roles and users List all roles

SELECT
    r.[name]                                    AS [Role]
    , m.[name]                                  AS [Member]
    , m.Create_date                             AS [Created Date]
    , m.modify_Date                             AS [Modified Date]
FROM
    [sys].[database_role_members]               AS rm
JOIN [sys].[database_principals]                AS r
    ON rm.[role_principal_id] = r.[principal_id]
JOIN [sys].[database_principals]                AS m
    ON rm.[member_principal_id] = m.[principal_id]
WHERE
    r.[type_desc] = 'DATABASE_ROLE'
ORDER BY
    r.[name], m.[name];
;

SELECT
    r.[name]                                    AS [Role]
    , ISNULL(m.[name], 'No members')            AS [Member]
    , m.create_date                             AS [Created Date]
    , m.modify_Date                             AS [Modified Date]
FROM
    [sys].[database_role_members]               AS rm
RIGHT OUTER JOIN [sys].[database_principals]    AS r
    ON rm.[role_principal_id] = r.[principal_id]
LEFT OUTER JOIN [sys].[database_principals]     AS m
    ON rm.[member_principal_id] = m.[principal_id]
WHERE
    r.[type] = 'R'
ORDER BY
    r.[name], ISNULL(m.[name], 'No members')
;

Example Results:

List relate roles and users List all roles

Role         |Member          |Created Date           |Modified Date          |
-------------+----------------+-----------------------+-----------------------+
DATA ENGINEER|demo@mail.com   |2022-11-15 00:00:00.000|2022-11-15 00:00:00.000|
db_owner     |dbo             |2003-04-08 00:00:00.000|2021-09-21 00:00:00.000|
db_owner     |admin@mail.com  |2021-05-12 00:00:00.000|2021-05-12 00:00:00.000|
db_ddladmin  |DATA ENGINEER   |2022-11-15 00:00:00.000|2022-11-15 00:00:00.000|
db_datareader|DATA ENGINEER   |2022-11-15 00:00:00.000|2022-11-15 00:00:00.000|
db_datawriter|DATA ENGINEER   |2022-11-15 00:00:00.000|2022-11-15 00:00:00.000|

Role         |Member          |Created Date           |Modified Date          |
-------------+----------------+-----------------------+-----------------------+
DATA ENGINEER|No members      |NULL                   |NULL                   |
db_owner     |dbo             |2003-04-08 00:00:00.000|2021-09-21 00:00:00.000|
db_owner     |admin@mail.com  |2021-05-12 00:00:00.000|2021-05-12 00:00:00.000|
db_ddladmin  |DATA ENGINEER   |2022-11-15 00:00:00.000|2022-11-15 00:00:00.000|
db_datareader|DATA ENGINEER   |2022-11-15 00:00:00.000|2022-11-15 00:00:00.000|
db_datawriter|DATA ENGINEER   |2022-11-15 00:00:00.000|2022-11-15 00:00:00.000|

---

Roles

Dedicate SQL Pool Serverless SQL Pool

CREATE ROLE [rolename]
EXEC sp_addrolemember 'rolename', [username@mail.com]
GO

CREATE ROLE [rolename];
ALTER ROLE [rolename] ADD MEMBER [username@mail.com];
GO

Note

If you want to remove user from this role,

Dedicate SQL Pool Serverless SQL Pool

EXEC sp_droprolemember '<role-name>', 'username@mail.com';

ALTER ROLE [role-name] DROP MEMBER [username@mail.com];

Generate a drop systax from query,

Dedicate SQL Pool

SELECT
  CONCAT(
        'EXEC sp_droprolemember ''', r.name, ''', ''', m.name, ''';'
  ) AS remove_member_command
FROM
    sys.database_role_members  AS rm
RIGHT OUTER JOIN sys.database_principals AS r
    ON rm.role_principal_id = r.principal_id
LEFT OUTER JOIN sys.database_principals AS m
    ON rm.member_principal_id = m.principal_id
WHERE
    r.type = 'R'
    AND LOWER(m.name) IN (
        'userbane@mail.com',
        ...
    )
ORDER BY
    r.name, ISNULL(m.name, 'No members')
;

---

Permissions

Getting Relations

List relate permissions List relate permissions with grantor

SELECT DISTINCT
     pr.principal_id                AS [ID],
     pr.[name]                      AS [User],
     pr.[type_desc]                 AS [Type],
     pr.authentication_type_desc    AS [Auth_Type]
     pe.state_desc                  AS [State]
     pe.permission_name             AS [Permission]
     pe.class_desc                  AS [Class]
     coalesce(o.[name], sch.name)   AS [Object]
FROM
    sys.database_principals         AS pr
JOIN sys.database_permissions       AS pe
    ON pe.grantee_principal_id = pr.principal_id
LEFT JOIN sys.objects               AS o
    ON o.object_id = pe.major_id
LEFT JOIN sys.schemas               AS sch
    ON sch.schema_id = pe.major_id
    AND class_desc = 'SCHEMA'
;

SELECT DISTINCT
    DB_NAME()                           AS [DB],
    p.[name]                            AS [User],
    p.[type_desc]                       AS [Type],
    p2.[name]                           AS [Grantor],
    pe.[state_desc]                     AS [State],
    pe.[permission_name]                AS [Permission],
    o.[Name]                            AS [Object],
    o.[type_desc]                       AS [Object Type]
FROM [sys].[database_permissions]       AS pe
LEFT JOIN [sys].[objects]               AS o
    ON pe.[major_id] = o.[object_id]
LEFT JOIN [sys].[database_principals]   AS p
    ON pe.[grantee_principal_id] = p.[principal_id]
LEFT JOIN [sys].[database_principals]   AS p2
    ON pe.[grantor_principal_id] = p2.[principal_id]
;

Example Results:

List relate permissions List relate permissions with grantor

ID|User              |Type          |Auth Type|State|Permission             |Class             |Object         |
--+------------------+--------------+---------+-----+-----------------------+------------------+---------------+
25|username@mail.com |EXTERNAL_USER |EXTERNAL |GRANT|CONNECT                |DATABASE          |               |
26|de_external       |DATABASE_ROLE |NONE     |GRANT|ALTER                  |SCHEMA            |DATAEXTERNAL   |
26|de_external       |DATABASE_ROLE |NONE     |GRANT|ALTER                  |SCHEMA            |MART           |
26|de_external       |DATABASE_ROLE |NONE     |GRANT|ALTER                  |SCHEMA            |CURATED        |
26|de_external       |DATABASE_ROLE |NONE     |GRANT|ALTER ANY DATA SOURCE  |DATABASE          |               |
26|de_external       |DATABASE_ROLE |NONE     |GRANT|ALTER ANY FILE FORMAT  |DATABASE          |               |
26|de_external       |DATABASE_ROLE |NONE     |GRANT|EXECUTE                |OBJECT_OR_COLUMN  |FUNC_CHK_ID    |
26|de_external       |DATABASE_ROLE |NONE     |GRANT|EXECUTE                |OBJECT_OR_COLUMN  |FUNC_CHK_TITLE |

DB      |User       |Type          |Grantor               |State|Permission                    |Object          |Object Type         |
--------+-----------+--------------+----------------------+-----+------------------------------+----------------+--------------------+
syndpdev|adfuser    |SQL_USER      |dbo                   |GRANT|ALTER                         |                |                    |
syndpdev|adfuser    |SQL_USER      |dbo                   |GRANT|ALTER ANY EXTERNAL DATA SOURCE|                |                    |
syndpdev|adfuser    |SQL_USER      |dbo                   |GRANT|ALTER ANY EXTERNAL FILE FORMAT|                |                    |
syndpdev|adfuser    |SQL_USER      |dbo                   |GRANT|CONNECT                       |                |                    |
syndpdev|de_vendor  |DATABASE_ROLE |dbo                   |GRANT|ALTER ANY EXTERNAL FILE FORMAT|                |                    |
syndpdev|de_vendor  |DATABASE_ROLE |dbo                   |GRANT|EXECUTE                       |                |                    |
syndpdev|de_vendor  |DATABASE_ROLE |user@email.com        |GRANT|EXECUTE                       |FUNC_CHK_ID     |SQL_SCALAR_FUNCTION |
syndpdev|de_vendor  |DATABASE_ROLE |user@email.com        |GRANT|EXECUTE                       |FUNC_CHK_TITLE  |SQL_SCALAR_FUNCTION |

---

Grant

Data ExecutionSpecific Table

GRANT CREATE VIEW TO [role-name];
GRANT CREATE PROCEDURE TO [role-name];
GRANT ALTER TO [role-name];

GRANT EXECUTE ON SCHEMA::<schema-name> TO [role-name];
GRANT UPDATE ON SCHEMA::<schema-name> TO [role-name];
GRANT INSERT ON SCHEMA::<schema-name> TO [role-name];
GRANT DELETE ON SCHEMA::<schema-name> TO [role-name];
GRANT ALTER ON SCHEMA::<schema-name> TO [role-name];

GRANT SELECT ON OBJECT::<schema-name>.<object-name> TO [role-name]

GRANT IMPERSONATE ON USER::<user-name> TO <user-name>;

Warning

Impersonate can not use on the Azure Synapse Serverless SQL Pool.

More Example for Grant Permissions

---

Workload

CREATE WORKLOAD GROUP <group-name>
WITH (
    MIN_PERCENTAGE_RESOURCE = 100,
    CAP_PERCENTAGE_RESOURCE = 100,
    REQUEST_MIN_RESOURCE_GRANT_PERCENT = 100
);

-- Classifies load_user with the workload group LoadData
CREATE WORKLOAD CLASSIFIER [<classifier-name>]
WITH (
    WORKLOAD_GROUP = '<group-name>',
    MEMBERNAME = '<username>'
);

Read Mores

• Microsoft Azure Synapse Analytics SQL Authentication
• Microsoft SQL T-SQL Create User Transaction