PowerShell script to add new user to Login of SQL server

Question

I should add a new user to Login of SQL server and should add roles "dbcreator", "public" and "securityadmin" to the user.

The above said requirement should be done automatically using powershell. Please let me know how to do this using powershell and what the script is to be written.

Answer 1

http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-programming/74816/powershell-add-role

http://dimantdatabasesolutions.blogspot.com/2010/09/be-careful-to-grant-dbcreator-server.html

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

I got the powershell script for the above. It is as follows

# Add the user into SQL. Be sure to change the SQL User of sa and password of my password.

[string]$err = sqlcmd -Usa -Pmypassword -d 'master' -Q "CREATE LOGIN `[$user`] FROM WINDOWS WITH DEFAULT_DATABASE=`[MASTER`], DEFAULT_LANGUAGE=`[us_english`]"

# This variable will become populated if an error occurred; else it will remain blank.

if ($err) { write-host "ERROR! The following error occurred while creating SQL User: $err " }

# The permission addition will execute if the above command was successful.

Else {

        # Add the credential of Sysadmin to the users. This can be changed to any role.

        [string]$err = sqlcmd -Usa -Pmypassword -d 'master' -Q "EXEC sys.sp_addsrvrolemember @loginame = N'$user', @rolename = N'sysadmin'"

        if ($err) { write-host "Error Assigning Permisssions to $user: $err" }

}