Do you want BuboFlash to help you learning these things? Or do you want to add or correct something? Click here to log in or create user.



Tags
#Agile #Docs #Powershell #SQL
Question
How does Get-SQLServerPrivileges function return the privileges for logins ?
Answer
function Get-SQLServerPrivileges
		{
		    <# 
		    .SYNOPSIS
		        Returns each server login with their server roles
		    .DESCRIPTION
		        This function will return all the logins on the database server
		        and check whether they are member of a server role.
		    .PARAMETER  instance
		        This is the instance that needs to be connected
		    .EXAMPLE
		        Get-SQLServerPrivileges "SQL01"
		    .EXAMPLE
		        Get-SQLServerPrivileges "SQL01\INST01"
		    .EXAMPLE
		        Get-SQLServerPrivileges -inst "SQL01\INST01"
		    .INPUTS
		    .OUTPUTS
		        System.Array
		    .NOTES
		    .LINK
		    #>
		    
		    param
		    (
		        [Parameter(Mandatory = $true, Position=1)]
		        [ValidateNotNullOrEmpty()]
		        [string]$inst = $null
		    )
		    
		    # Check if assembly is loaded
		    Load-Assembly -name 'Microsoft.SqlServer.SMO'
		 
		    # Check if the instance object is already initiated
		    if($server -eq $null)
		    {
		        try{
		            $server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $inst
		        }
		        catch [Exception]
		        {
		            Write-Host "$_.Exception.GetType().FullName, $_.Exception.Message" -ForegroundColor Red
		        }
		    }
		 
		    # Create the result array
		    $result = @()
		 
		    # Create the array for the server roles
		    $serverRoles = @()
		 
		    # Get all the logins
		    $logins = $server.Logins
		 
		    # Loop through the logins
		    foreach($login in $logins)
		    {
		        
		        if(($login.Name -notlike "##*"))
		        {
		            # Get all the server
		            $serverRoles = ($login.ListMembers()) -join ","
		 
		            # Make the result
		            if($serverRoles.Count -gt 1)
		            {
		                $result += $login | Select `
		                             Name,LoginType,CreateDate,DateLastModified,IsDisabled,`
		                             @{N="ServerRoles";E=([string]::Join(",", $serverRoles))} | Sort-Object Name 
		            }
		            else
		            {
		                $result += $logn | Select `
		                             Name,LoginType,CreateDate,DateLastModified,IsDisabled,`
		                             @{N="ServerRoles";E={$serverRoles}} | Sort-Object Name 
		            }
		 
		            # Clear the array
		            $serverRoles = @()
		        }
		    }
		 
		    return $result
		 
	} 

[default - edit me]


Tags
#Agile #Docs #Powershell #SQL
Question
How does Get-SQLServerPrivileges function return the privileges for logins ?
Answer
?

Tags
#Agile #Docs #Powershell #SQL
Question
How does Get-SQLServerPrivileges function return the privileges for logins ?
Answer
function Get-SQLServerPrivileges
		{
		    <# 
		    .SYNOPSIS
		        Returns each server login with their server roles
		    .DESCRIPTION
		        This function will return all the logins on the database server
		        and check whether they are member of a server role.
		    .PARAMETER  instance
		        This is the instance that needs to be connected
		    .EXAMPLE
		        Get-SQLServerPrivileges "SQL01"
		    .EXAMPLE
		        Get-SQLServerPrivileges "SQL01\INST01"
		    .EXAMPLE
		        Get-SQLServerPrivileges -inst "SQL01\INST01"
		    .INPUTS
		    .OUTPUTS
		        System.Array
		    .NOTES
		    .LINK
		    #>
		    
		    param
		    (
		        [Parameter(Mandatory = $true, Position=1)]
		        [ValidateNotNullOrEmpty()]
		        [string]$inst = $null
		    )
		    
		    # Check if assembly is loaded
		    Load-Assembly -name 'Microsoft.SqlServer.SMO'
		 
		    # Check if the instance object is already initiated
		    if($server -eq $null)
		    {
		        try{
		            $server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $inst
		        }
		        catch [Exception]
		        {
		            Write-Host "$_.Exception.GetType().FullName, $_.Exception.Message" -ForegroundColor Red
		        }
		    }
		 
		    # Create the result array
		    $result = @()
		 
		    # Create the array for the server roles
		    $serverRoles = @()
		 
		    # Get all the logins
		    $logins = $server.Logins
		 
		    # Loop through the logins
		    foreach($login in $logins)
		    {
		        
		        if(($login.Name -notlike "##*"))
		        {
		            # Get all the server
		            $serverRoles = ($login.ListMembers()) -join ","
		 
		            # Make the result
		            if($serverRoles.Count -gt 1)
		            {
		                $result += $login | Select `
		                             Name,LoginType,CreateDate,DateLastModified,IsDisabled,`
		                             @{N="ServerRoles";E=([string]::Join(",", $serverRoles))} | Sort-Object Name 
		            }
		            else
		            {
		                $result += $logn | Select `
		                             Name,LoginType,CreateDate,DateLastModified,IsDisabled,`
		                             @{N="ServerRoles";E={$serverRoles}} | Sort-Object Name 
		            }
		 
		            # Clear the array
		            $serverRoles = @()
		        }
		    }
		 
		    return $result
		 
	} 

[default - edit me]

If you want to change selection, open document below and click on "Move attachment"

Documenting SQL Server with PowerShell - Simple Talk
B function: Get-SQLServerPrivileges Most of the functions in PSSQLLIB use properties of the SMO server object. A few of the functions use T-SQL queries to retrieve the data. Listing 2 shows the <span>Get-SQLServerPrivileges function, to return the privileges for logins. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65

Summary

statusnot learnedmeasured difficulty37% [default]last interval [days]               
repetition number in this series0memorised on               scheduled repetition               
scheduled repetition interval               last repetition or drill

Details

No repetitions


Discussion

Do you want to join discussion? Click here to log in or create user.