Get Objects in the deny roles in Sql Server via Powershell

Quick script to find all the objects (Users, Groups) in the *deny* roles in all databases. Handy for when your AD account setups are heavily nested with duplicate users in multiple conflicting groups . If you find yourself having to use deny in order to get permissions correct, it’s probably time you flattened out your groups a bit. Using DENY can be a chore to track down. This just spits out a gridview of the members of the *deny* roles.

Import-Module SqlPS -DisableNameChecking

$objects = @();
$servers = gc -Path c:\Servers.txt

try{

    $Servers | %{
        $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $_
        $srv.Databases | where{$_.Status -eq [Microsoft.SqlServer.Management.Smo.DatabaseStatus]::Normal} | %{
            $dbName = $_.Name 
            $_.Roles | where{$_.Name -like '*deny*'} | %{
                $roleName = $_.Name 
                $roleMembers = $_.EnumMembers()
                $roleMembers | %{
                    $objects += [PSCustomObject] @{
                        ServerName = $srvName
                        DatabaseName = $dbName
                        Role = $roleName
                        RoleMember = $_
                    }
                }
            }
        }
    }
    $objects | Out-GridView
}
catch{
    $_ | fl -Force
}

Change the sa password in Sql Server via Powershell

Here is how you change the sa password for your sql servers via powershell. Use with caution, marginally tested. Make sure your sa password is somewhat complex or this will throw an exception.

import-module SqlPS -DisableNameChecking
[string]$newPwd = 'C0mp13xP@55word%'


try{
    gc -Path c:\Servers.txt | %{

        $srvName = $_
        $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvName
        $srv.Logins | where{$_.Name -eq 'sa'} | %{
            $_.ChangePassword($newPwd);
        }
    }
}
catch{
    $_ | fl -Force
}

Add a linked server & remote login to Sql Server via Powershell

Sort of. Trying to do this via just SMO and powershell was immensely frustrating when dealing with the remotelogin security. What I wanted the login to do was to use a sql login no matter the context (the bottom-most radio button in the screenshot below).

Alas, every time I set the remotelogin property and called the SetRemotePassword() method it would automatically add it to the Local server login to remote server login mappings list.

Not what I want. In order to determine the proper…properties to set I created a new linked server and scripted it out to have a gander at the properties.

Two properties stood out. The @locallogin and the @useself. Okay, cool. Easy enough, the smo linkedserverlogin has a properties collection in it, should be easy enough to set. Except, the bloody properties never get populated. Even after calling the Initialize($true) on the LinkedServerLogin smo object, said properties are not there.

Okay, let’s add them. Nope. There is no Add() method on the SqlPropertyCollection (or on the smo PropertyCollection inherited class). In the end, I ended up just having to execute a sql call in order to get this to work, as I’d already spent 4 stinking hours trying to get this to work correctly. I detest having to resort to doing this, but I HAVE to move on.

Here it is. If you have this figured, please please let me know what the poop is.

Import-Module sqlps -DisableNameChecking

$srvName = 'ServerName'
$linkedServerLogin = 'LinkedServerLoginName'
$pwd = 'LinkedServerPassword'
$servers = gc -Path C:\LinkedServers.txt

cls

try{
    $srv = New-Object Microsoft.SqlServer.Management.smo.server $srvName
    $servers | %{
        if($srv.LinkedServers.Contains($_)){
            return;
        }
        
        $linkedServer = New-Object Microsoft.SqlServer.Management.Smo.LinkedServer 
        $linkedServer.Parent = $srv
        $linkedServer.Name = $_
        $linkedServer.DataSource = $_
        #$srv.LinkedServers.Add($linkedServer);
        $linkedServer.Create();

        $sql = "
        USE [master]
        GO
        EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'$($_)', @locallogin = NULL , @useself = N'False', @rmtuser = N'$($linkedServerLogin)', @rmtpassword = N'$($pwd)'
        GO
        "
        $srv.ConnectionContext.ExecuteNonQuery($sql);

    }
}
catch{
    $_ | fl -Force
}
finally{

}

Turn off AutoShrink on databases via powershell

Easy peasy script to turn off AutoShrink on all databases. I’ll let Paul Randal do the talking as to why you shouldn’t have autoshrink turned on.

Import-Module sqlps -DisableNameChecking

$ignoreDBs = @('IgnoreDB')

try{
    gc -Path 'c:\Servers.txt' | %{
        $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $_
        $srv.Databases | where{-not $_.IsSystemObject -and $_.Name -notin $ignoreDBs} | %{
            if($_.AutoShrink -eq $true){
                $_.AutoShrink = $false
                $_.Alter();
            }
        }        
    }
}
catch{
    $srvName
    $_ | fl -Force
}
finally{

}

Expand Windows Groups on Sql Server

Quick post on how to expand windows groups to show their sub-groups and logins in sql server. This just writes the group hierarchy to the console. Use at your own risk.

import-module activedirectory

function Get-GroupHierarchy{
    param(
        [Parameter(Mandatory=$true)]
        [String]$searchGroup
    )
    $outputs = @();
    [int]$i++ | out-null;

    get-adgroupmember $searchGroup | sort-object objectClass -descending | %{
        $output = new-object -TypeName PSObject -Property @{
            Parent = $searchGroup
            GroupName = $_.Name
            Type = $_.objectClass
            Hierarchy = $i
        }
        $outputs += $output

        if($_.ObjectClass -eq 'group'){
            $outputs += Get-GroupHierarchy $_.name
        }
    }
    return $outputs;
}

cls
$srvName = 'ServerName'
$srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
$srvConn.ServerInstance = $srvName
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn
$ignoreGroups = @('NT SERVICE\MSSQLSERVER', 'NT SERVICE\SQLSERVERAGENT');

$srv.Logins | where{$_.LoginType -eq [Microsoft.SqlServer.Management.Smo.LoginType]::WindowsGroup -and $_.Name -notin $ignoreGroups} | %{
    $loginName = $_.Name.Replace('TRX0\', '')
    Write-Host "Windows Group:  $loginName" -ForegroundColor Green
    Get-GroupHierarchy $loginName | ft -AutoSize   
    Write-Host "`n`r"
}

Calling sp_start_job via Linked Server

If you’re trying to execute sp_start_job via a linked server and keep getting the error:

Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 67
The specified @job_name (‘InsertJobNameHere’) does not exist.

,then here are the steps you need to follow to resolve the issue:

First, take stock of your life and ask yourself why you’re doing this in the first place; use an SSIS package man!
Find the login that is being used for the linked server.
Really, really question whether this is the best option for accomplishing the task you’re trying to solve.
On the target server, add the login from step 2 to the msdb database on the target server.
Add the login to the SQLAgentOperatorRole & TargetServersRole on the target server.
Grant EXEC to sp_start_job stored procedure in MSDB to the TargetServersRole on the target server.

USE [msdb]
GO
CREATE USER [linkedServerLogin] FOR LOGIN [linkedServerLogin]
GO
USE [msdb]
GO
EXEC sp_addrolemember N'TargetServersRole', N'linkedServerLogin'
GO
USE [msdb]
GO
EXEC sp_addrolemember N'SQLAgentOperatorRole', N'linkedServerLogin'
GO
GRANT EXEC ON sp_start_job TO [linkedServerLogin]

Drop Linked Server from All Servers

Quick script on how to drop a linked server from a list of servers. Minimally tested, use at your own risk.

 $LinkedServerName = 'LinkedServerToFindAndDrop'

try{
    gc -Path c:\Servers.txt | %{

        $srvName = $_
	    $srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
	    $srvConn.ServerInstance = $srvName
	    $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn

        $linkedServer = $srv.LinkedServers | where{$_.DataSource -eq $LinkedServerName} 
        if($linkedServer -ne $null){
            for([int]$i = 0; $i -lt $linkedServer.LinkedServerLogins.Count; $i++){
                $login = $linkedServer.LinkedServerLogins.item($i);
                $login.Drop();
            }

            $linkedServer.Drop();
        }
    }

}
catch{
    $_ | fl -Force
}

Scientific Notation and the ISNUMERIC function

Fun problem at work today. Someone was importing some data, and the value 2d4 kept evaluating as 1 using the ISNUMERIC function. The reason why this evaluates true, is because 2d4 is technically a number using scientific notation. The ‘d’ stands for double-precision. 2e4 would also return 1 (exponential value), as would –12, +12, or $12.

In order to correctly check to see if the characters are numeric (in this case, the first 3 characters of a string) you could use this:

DECLARE @Test NVARCHAR(100) = '2D4jlkafkjl'

SELECT 
	CASE
	WHEN LEFT(@Test,3) LIKE '[0-9][0-9][0-9]' THEN 
		CAST(LEFT(@Test, 3) AS INT)
ELSE
999
END

Alternatively, if you want to check to see if a string has any letters at all in it, you could just do this:

DECLARE @Test NVARCHAR(100) = '11i111'

SELECT 
	CASE
	WHEN @Test NOT LIKE '%[^0-9]%' THEN 'all numbers'
ELSE
'has letters'
END

Associate User & Logins in Sql Server via Powershell

Quick script to re-associate logins with users that have the same name. If the user in the database is named the same as the login and has no login currently associated with it, it will set the database user to use the login with the same name. Apparently, you can’t do this directly via powershell by setting the users’ login to the login name, as it errors out with “Modifying the Login property of the User object is not allowed. You must drop and recreate the object with the desired property.” Hence the SQL call to sync up the user.

[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

$serverName = 'ServerName'

try{
	$srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
	$srvConn.ServerInstance = $serverName
	$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn
	
	$srv.Logins | where{$_.LoginType -eq [Microsoft.SqlServer.Management.Smo.LoginType]::SqlLogin} | %{
		$login = $_
		$srv.Databases | %{
			if($_.Users.Contains($login.Name)){
				$user = $_.Users[$login.Name];
				if($user.Login -eq ''){
<#					#can't do this apparently, smo will only let you drop & re-create...
					$user.Login = $login.Name;
					$user.Alter();
#>
					$_.ExecuteNonQuery("sp_change_users_login 'auto_fix', '" + $user.Name + "'")
				}
			}
		}
	}
}
catch{
	$_ | fl -Force
}

Add a Database Role and Grant EXEC

Quick post on how to add a database role to all your databases and grant EXEC to said role. This will also add the specified user to said role (aptly named db_exec). Use with caution, minimally tested.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
 
$servers = @('ServerName')
$roleName = 'db_exec'
$LoginName = 'LoginName'; 

 
 try{
	foreach($server in $servers){
	 
	    $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server
	 
	    if (-not $srv.Logins.Contains($LoginName)){ 
	       throw "Login $LoginName does not exist on server $server"
		   return;
		}
		
	    $login = $srv.Logins[$LoginName]
	 
	    foreach($database in $srv.Databases | where{-not $_.IsSystemobject -and $ignoreDBs -notcontains $_.Name -and $_.Status -eq [Microsoft.SqlServer.Management.Smo.DatabaseStatus]::Normal}){
		
			if(-not $database.Users.Contains($login.Name)){
				$user = New-Object('Microsoft.SqlServer.Management.Smo.User') $database, $login.Name
				$user.Login = $login.Name
	        	$user.create();
			}
			
			$user = $database.Users[$LoginName]
			
			if(-not $database.Roles.Contains($roleName)){
				$role = New-Object('Microsoft.SqlServer.Management.smo.DatabaseRole') $database, $roleName
				$role.Create();
			}
			
			$role = $database.Roles[$roleName]
			$role.AddMember($user.Name);
			
			$objDbPerms = New-Object Microsoft.SqlServer.Management.Smo.DatabasePermissionSet 
			$objDbPerms.Execute = $true
			$database.Grant($objDbPerms, $roleName);
			$database.Alter();
	    }
	}
}
catch{
	$_ | fl -Force
}