This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.
Today, we worked on a service request that our customer needs to use sp_validatelogins to verify if the user exists or not in their windows environment.
Right now this function doesn't work in Azure SQL Database returning the following error message: Msg 2812, Level 16, State 62, Line 10
Could not find stored procedure 'sp_validatelogins'. In this article I would like to share a possible workaround.
Well, the first thing that I found was, what is the TSQL script behing this function, for this reason, running sp_helptext 'sp_validatelogins' I saw the following content.
create procedure sys.sp_validatelogins
AS
-- Must be securityadmin (or sysadmin) to execute
if is_srvrolemember('securityadmin') = 0 and is_srvrolemember('sysadmin') = 0
begin
raiserror(15247,-1,-1)
return 1
end
-- Use get_sid() to determine if nt name is still valid (builtin is only available from system procs!)
select 'SID' = sid, 'NT Login' = loginname from master.dbo.syslogins
where isntname = 1 and get_sid(loginname) is null
return 0 -- sp_validatelogins
I found that dbo.syslogins is an invalid object name in Azure SQL Database, so, I need to obtain the users in another way. In this case, I'm going to use the DMV per database called select * from sys.database_principals where type IN ( 'X','E'), this DMV will filter all the AAD groups and users to validate.
Finally, using Get-AzADUser to find if the user or group exists. In my proof of concept, I wrote this PowerShell script that connecting to any user database, reads all the AAD users and groups and check if exists or not.
Connect-AzAccount -Subscription "xxxxxx-xxx-xxx-xxx-xxxxx"
$server = "servername.database.windows.net" #ServerName parameter to connect,for example, myserver.database.windows.net
$user = "username" #UserName parameter to connect
$password = "password" #Password Parameter to connect
$Db = "databasename" #DBName Parameter to connect.
#----------------------------------------------------------------
#Function to connect to the database using a retry-logic
#----------------------------------------------------------------
Function GiveMeConnectionSource()
{
for ($i=1; $i -lt 10; $i++)
{
try
{
logMsg( "Connecting to the database..." + $DB + ". Attempt #" + $i) (1)
$SQLConnection = New-Object System.Data.SqlClient.SqlConnection
$SQLConnection.ConnectionString = "Server="+$server+";Database="+$Db+";User ID="+$user+";Password="+$password+";Connection Timeout=60;Application Name=PerfCollector"
$SQLConnection.Open()
logMsg("Connected to the database.." + $DB) (1)
return $SQLConnection
break;
}
catch
{
logMsg("Not able to connect - " + $DB + " - Retrying the connection..." + $Error[0].Exception) (2)
Start-Sleep -s 5
}
}
}
function bExistAADLoginUser($Name,$Type)
{
If($Type -eq 'E' )
{
logMsg("Checking the User...") (3)
$Data = Get-AzADUser -Mail $Name
}
If($Type -eq 'X' )
{
logMsg("Checking the Group...") (3)
$Data = Get-AzADUser -DisplayName $Name
}
return $Data.Count
}
#--------------------------------
#Log the operations
#--------------------------------
function logMsg
{
Param
(
[Parameter(Mandatory=$true, Position=0)]
[string] $msg,
[Parameter(Mandatory=$false, Position=1)]
[int] $Color,
[Parameter(Mandatory=$false, Position=2)]
[boolean] $Show=$true,
[Parameter(Mandatory=$false, Position=3)]
[boolean] $bShowDate=$true
)
try
{
if($bShowDate -eq $true)
{
$Fecha = Get-Date -format "yyyy-MM-dd HH:mm:ss"
$msg = $Fecha + " " + $msg
}
$Colores="White"
$BackGround =
If($Color -eq 1 )
{
$Colores ="Cyan"
}
If($Color -eq 3 )
{
$Colores ="Yellow"
}
if($Color -eq 2 -And $Show -eq $true)
{
Write-Host -ForegroundColor White -BackgroundColor Red $msg
}
else
{
if($Show -eq $true)
{
Write-Host -ForegroundColor $Colores $msg
}
}
}
catch
{
Write-Host $msg
}
}
#--------------------------------
#Validate Param
#--------------------------------
function TestEmpty($s)
{
if ([string]::IsNullOrWhitespace($s))
{
return $true;
}
else
{
return $false;
}
}
try
{
Clear
logMsg("-------------------- Header Filter details --------------") (1)
logMsg(" ServerName: " + $server) (1)
logMsg(" DB: " + $DB) (1)
logMsg("-------------------- Footer Filter details --------------") (1)
$SQLConnectionSource = GiveMeConnectionSource
if($SQLConnectionSource -eq $null)
{
logMsg("It is not possible to connect to the database") (2)
exit;
}
$commandDB = New-Object -TypeName System.Data.SqlClient.SqlCommand
$commandDB.CommandTimeout = 6000
$commandDB.Connection=$SQLConnectionSource
$commandDB.CommandText = "select name, type, principal_id from sys.database_principals where type IN ( 'X','E')"
$ReaderDB = $commandDB.ExecuteReader();
while($ReaderDB.Read())
{
logMsg("Checking the ADD object:" + $ReaderDB.GetValue(0).ToString() + " with PrincipalID: " + $ReaderDB.GetValue(2).ToString() ) (1)
$Count = bExistAADLoginUser $ReaderDB.GetValue(0).ToString() $ReaderDB.GetValue(1).ToString()
If( $Count -eq 0 )
{
logMsg("The AAD Object doesn't exist:" + $ReaderDB.GetValue(0).ToString()) (2)
}
}
$ReaderDB.Close();
$SQLConnectionSource.Close()
}
catch
{
logMsg("sp_validatelogins Script was executed incorrectly ..: " + $Error[0].Exception) (2)
}
finally
{
logMsg("sp_validatelogins Script finished - Check the previous status line to know if it was success or not") (2)
}
Basically, executing in any user or master database in Azure SQL Database I was able to identify and obtain the same results of this function in On-Premises.
Enjoy!
