-2

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.

Manvendra Bele
  • 23
  • 2
  • 7
  • 1
    Good luck. If you get stuck post your code. – David Rushton Sep 19 '17 at 15:37
  • Hello, you will want to look at the SQL Server and SQLPS modules that are available for PowerShell. SO members will not build a script for you, but if you put something together we would gladly assist you with any problems you encounter. Google is also a good place to start :) – cet51 Sep 19 '17 at 15:38
  • 1
    Stack Overflow is not a code writing service. If you need help learning how to code or troubleshoot something then this is the place for you. If you need code from scratch then you should hire a developer. – ArcSet Sep 19 '17 at 15:39
  • What have you tried, and how has what you've tried failed? Ideally, you should provide a [Minimal, Complete, and Verifiable Example](https://stackoverflow.com/help/mcve) of what you've tried, and include specific information on how it failed, with error messages and/or erroneous output. SO is not a code-writing service; the best questions are those which provide useful information so that those who answer can guide you to devising your own correct answer. See [How to Ask a Good Question](https://stackoverflow.com/help/how-to-ask). – Jeff Zeitlin Sep 19 '17 at 15:40
  • please give it a try before posting here. its not a free coding service! and for your case there are so many solutions in our world wide web – Esteban P. Sep 19 '17 at 15:40
  • i am new to powershell. so i thought if some one can help me with general powershell script to achieve above task. – Manvendra Bele Sep 19 '17 at 15:42
  • see this - https://stackoverflow.com/questions/46216793/t-sql-statements-to-grant-access-and-create-account -- how script is provided to help user. Script will not only help me but many users in future who come across same problem. – Manvendra Bele Sep 19 '17 at 15:45
  • Give a man a piece of working code and you solve his problem. Teach a man to write code and you give him a lifetime of new problems and possibilities. – Bruce Sep 19 '17 at 16:42

1 Answers1

0

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.

Sage Pourpre
  • 9,932
  • 3
  • 27
  • 39
  • i have created this script – Manvendra Bele Sep 21 '17 at 11:38
  • $svr = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $servers $srv.ConnectionContext.LoginSecure=$true; $srv.ConnectionContext.ConnectAsUser = $true; $srv.ConnectionContext.set_Login("service-account"); $srv.ConnectionContext.set_Password("Password"); $databasenames = "TestDB1", "TestDB2" $loginName = "Jsmith" if ($loginName -eq $Null) {$Login = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Login -ArgumentList $svr, $Username $Login.LoginType = 'WindowsUser' $Login.Create() $LoginName - $Login.Name} – Manvendra Bele Sep 21 '17 at 11:38