CODEWITHSUNDEEP
sql-serverpowershellroles
Arvind Aryans
Arvind Aryans

Reputation: 61

Add user to server role in SQL Server

I am trying to add a user to a SQL Server role using PowerShell.

I have a script which reads the server, user and role information and puts it in a text file. I am able to connect to the server and check for the user availability in that server, however I am not able to add the server role to the user. Please assist with the correct script to add the role. My script is long and not able to put it here. Is there a way I can attach it as a text file?

Upvotes: 5

Views: 4188

Answers (2)

Cobus Kruger
Cobus Kruger

Reputation: 8605

I found a very simple listing here.

This uses a Windows domain user, but there is no reason it shouldn't work for SQL Server users as well. Anyway, here goes:

$sqlSrv = New-Object 'Microsoft.SqlServer.Management.Smo.Server' "SQLServer\Instance"

$login = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Login -ArgumentList $sqlSrv, "DOMAIN\username"
$login.LoginType = "WindowsUser"

$login.AddToRole("MyServerRole")
$login.Alter()

If you also need to create the login in the same script (I don't think that's the OP's case), you'll just add $login.Create() before the AddToRole call.

Upvotes: 0

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8043

The Add-UserToRole function takes four parameters Server, Database, User and Role and does a series of error checks.

suppose you have the following files with user information

enter image description here enter image description here

and use them with the Add-SQLAccountToSQLRole and Add-UserToRole functions to create the users. try this

enter image description here

Powershell result will be as follows

enter image description here

Check Here for more details and complete code

Upvotes: 3

Related Questions