user-managment.sql

--For Azure SQL Server, "external" users typically refer to Azure Active Directory (Microsoft Entra ID) users, as opposed to SQL authentication users. Here are the queries:
--List All External (Azure AD/Entra ID) Users
-- Get all Azure AD users in the current database
SELECT 
    name AS username,
    type_desc AS user_type,
    authentication_type_desc AS auth_type,
    create_date,
    modify_date
FROM sys.database_principals
WHERE authentication_type_desc = 'EXTERNAL'
ORDER BY name;


-- Detailed view of external users with additional info
SELECT 
    dp.name AS username,
    dp.type_desc AS principal_type,
    dp.authentication_type_desc AS auth_type,
    dp.sid AS security_id,
    dp.create_date,
    dp.modify_date,
    dp.default_schema_name
FROM sys.database_principals dp
WHERE dp.authentication_type_desc = 'EXTERNAL'
    AND dp.type IN ('E', 'X', 'S', 'U')  -- E=External user, X=External group, S=SQL user, U=Windows user
ORDER BY dp.type_desc, dp.name;


-- Show all users with their authentication types
SELECT 
    name AS username,
    type_desc AS user_type,
    authentication_type_desc AS auth_type,
    CASE 
        WHEN authentication_type_desc = 'EXTERNAL' THEN 'Azure AD User/Group'
        WHEN authentication_type_desc = 'INSTANCE' THEN 'SQL Authentication'
        ELSE authentication_type_desc
    END AS description,
    create_date
FROM sys.database_principals
WHERE type IN ('E', 'X', 'S', 'U', 'G')
ORDER BY authentication_type_desc, name;


-- Get external users and their database role memberships
SELECT 
    dp.name AS username,
    dp.authentication_type_desc AS auth_type,
    STRING_AGG(r.name, ', ') AS roles
FROM sys.database_principals dp
LEFT JOIN sys.database_role_members drm ON dp.principal_id = drm.member_principal_id
LEFT JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
WHERE dp.authentication_type_desc = 'EXTERNAL'
GROUP BY dp.name, dp.authentication_type_desc, dp.create_date
ORDER BY dp.name;

/*
Key Points:
authentication_type_desc = 'EXTERNAL' identifies Azure AD/Entra ID users
type_desc = 'EXTERNAL_USER' (type='E') - Individual Azure AD users
type_desc = 'EXTERNAL_GROUP' (type='X') - Azure AD security groups
authentication_type_desc = 'INSTANCE' identifies SQL authentication users
*/



-- Check what permissions a user has
DECLARE @username NVARCHAR(128) = 'user@domain.com';

SELECT 
    USER_NAME(grantee_principal_id) AS grantee,
    OBJECT_SCHEMA_NAME(major_id) AS schema_name,
    OBJECT_NAME(major_id) AS object_name,
    permission_name,
    state_desc
FROM sys.database_permissions
WHERE USER_NAME(grantee_principal_id) = @username
    AND major_id > 0
ORDER BY schema_name, object_name, permission_name;



--1. EXECUTE - Run the stored procedure
GRANT EXECUTE ON dbo.MyStoredProcedure TO [user@domain.com];
--This allows the user to run the procedure with whatever it does (SELECT, INSERT, UPDATE, DELETE, etc.)


--Using a Database Role (Best Practice)
-- Create a role for viewing definitions
CREATE ROLE [DefinitionViewer];

-- Grant VIEW DEFINITION at database level to the role
GRANT VIEW DEFINITION TO [DefinitionViewer];

-- Add external users to this role
ALTER ROLE [DefinitionViewer] ADD MEMBER [user@domain.com];
ALTER ROLE [DefinitionViewer] ADD MEMBER [anotheruser@domain.com];