Search code examples
sqlsql-serverpowershellpowershell-remoting

Powershell with SQL

I want to create a PowerShell script to do following

1) Create Login/User in SQL 2) Grant Access to Database 3) Remove Access to Database

Process will be like this

1) Connect to SQL Server (Instance) -> Connect to Database in that instance -> See if Login/User Exist -> if No -> Create login -> Create user -> Grant Access to Database.

Note:

1) User are Windows 2) Sometimes I will like to connect to multiple SQL instance. So script should also check if the mentioned database are present in that instance.

Solution

  • The command you will mainly need for this is : Invoke-SQLcmd (documentation)

    If you want to be able to call this command though and you don't have SSMS installed, you will need the PowershellTools and the SharedManagementObject packages from the SQL features pack

    Here's a sample of its usage. 

    $infos = @{
     'ServerInstance'='YourServer';
     'Database' = 'master'; 
     'Username' = 'saOrAccountwithSufficientRights';
     'Password' = 'password' 
} 


$cmd = invoke-sqlcmd @infos -Query 'Select * from sys.databases'

# to see output in a table
$cmd | Format-Table

    From there, you can easily call SQL and do whatever logic you want from Powershell.

    .

    That being said,

    from your description, you seems to be already familiar with SQL while not at all with Powershell. You could opt to go through the SQLCMD route for more scripting possibilities in your SQL, which will allows you to use dynamic variables and do pretty much all logic you wish to do from your question (including iterating more than one server through the same script)

    SQLcmd example

    (reference: SQLAuthority

    Some SQLCMD informations: SQLCMD Utility

    Of course, if you d'rather have it in Powershell, then learn a few basic, make some attempts and ask questions providing sample of your code and where you're stuck and we will be able to help you based on that.