Home
%3CLINGO-SUB%20id%3D%22lingo-sub-736807%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%2393%3A%20Defining%20a%20custom%20SQL%20role%20to%20someone%20logging%20in%20via%20Azure%20AD%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-736807%22%20slang%3D%22en-US%22%3E%3CP%3EToday%2C%20I%20worked%20on%20a%20service%20request%20where%20our%20customer%20asked%20is%20it%20possible%20to%20define%20a%20custom%20SQL%20role%20to%20someone%20logging%20in%20via%20Azure%20Active%20Directory%20who%20is%20in%20the%20Admins%20group%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20Azure%20SQL%20Database%20we%20have%20two%20administrator%20users%3A%20SQL%20Logins%20and%20Azure%20Active%20Directory%20that%20could%20be%20a%20AAD%20User%20or%20AAD%20Group.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHowever%2C%26nbsp%3Bwe%20couldn't%20see%20this%20user%26nbsp%3Bin%20the%20security%20list%20of%20the%20database%26nbsp%3Band%20we%20cannot%20add%20this%20administrator%20user%20as%20a%20custom%20SQL%20role.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20this%20situation%2C%20I%20noticed%20that%20running%20SELECT%20USER_NAME()%2C%20this%20command%20returns%20dbo%20and%2C%20at%20least%2C%20you%20could%20identify%20this%20type%20of%26nbsp%3Bgroup.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%26nbsp%3Bgiving%20more%20context%20in%20this%20situation%2C%20the%20idea%20is%20to%20use%20Row%20Level%20Security%20to%20an%20specific%20group%20and%20specific%20Admin%20Users%2C%20for%20example%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000ff%22%20face%3D%22Consolas%22%20size%3D%222%22%3EALTER%3C%2FFONT%3E%20%3CFONT%20color%3D%22%230000ff%22%20face%3D%22Consolas%22%20size%3D%222%22%3EFUNCTION%3C%2FFONT%3E%3CFONT%20face%3D%22Consolas%22%20size%3D%222%22%3E%20%5Bfn_access%5D%3C%2FFONT%3E%3CFONT%20color%3D%22%23808080%22%20face%3D%22Consolas%22%20size%3D%222%22%3E()%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000ff%22%20face%3D%22Consolas%22%20size%3D%222%22%3ERETURNS%3C%2FFONT%3E%20%3CFONT%20color%3D%22%230000ff%22%20face%3D%22Consolas%22%20size%3D%222%22%3ETABLE%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000ff%22%20face%3D%22Consolas%22%20size%3D%222%22%3EWITH%3C%2FFONT%3E%20%3CFONT%20color%3D%22%230000ff%22%20face%3D%22Consolas%22%20size%3D%222%22%3ESCHEMABINDING%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAS%3C%2FP%3E%0A%3CP%3E%3CLI-WRAPPER%3E%3C%2FLI-WRAPPER%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000ff%22%20face%3D%22Consolas%22%20size%3D%222%22%3ERETURN%3C%2FFONT%3E%20%3CFONT%20color%3D%22%230000ff%22%20face%3D%22Consolas%22%20size%3D%222%22%3ESELECT%3C%2FFONT%3E%3CFONT%20face%3D%22Consolas%22%20size%3D%222%22%3E%201%20%3C%2FFONT%3E%3CFONT%20color%3D%22%230000ff%22%20face%3D%22Consolas%22%20size%3D%222%22%3EAS%3C%2FFONT%3E%3CFONT%20face%3D%22Consolas%22%20size%3D%222%22%3E%20fn_accessResult%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000ff%22%20face%3D%22Consolas%22%20size%3D%222%22%3EWHERE%3C%2FFONT%3E%26nbsp%3B%3CFONT%20color%3D%22%23ff00ff%22%20face%3D%22Consolas%22%20size%3D%222%22%3EUSER_NAME%3C%2FFONT%3E%3CFONT%20color%3D%22%23808080%22%20face%3D%22Consolas%22%20size%3D%222%22%3E()%3D%3C%2FFONT%3E%3CFONT%20color%3D%22%23ff0000%22%20face%3D%22Consolas%22%20size%3D%222%22%3E'dbo'%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEnjoy!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-736807%22%20slang%3D%22en-US%22%3E%3CP%3EToday%2C%20I%20worked%20on%20a%20service%20request%20where%20our%20customer%20asked%20is%20it%20possible%20to%20define%20a%20custom%20SQL%20role%20to%20someone%20logging%20in%20via%20Azure%20Active%20Directory%20who%20is%20in%20the%20Admins%20group%3F%3C%2FP%3E%3C%2FLINGO-TEASER%3E

Today, I worked on a service request where our customer asked is it possible to define a custom SQL role to someone logging in via Azure Active Directory who is in the Admins group?

 

In Azure SQL Database we have two administrator users: SQL Logins and Azure Active Directory that could be a AAD User or AAD Group.

 

However, we couldn't see this user in the security list of the database and we cannot add this administrator user as a custom SQL role.

 

In this situation, I noticed that running SELECT USER_NAME(), this command returns dbo and, at least, you could identify this type of group.

 

For giving more context in this situation, the idea is to use Row Level Security to an specific group and specific Admin Users, for example:

 

ALTER FUNCTION [fn_access]()

RETURNS TABLE

WITH SCHEMABINDING

 

AS

RETURN SELECT 1 AS fn_accessResult

WHERE USER_NAME()='dbo'

 

Enjoy!