none
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.

    Wednesday, May 11, 2011 9:43 AM

Answers

  • 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" }

    }

    • Marked as answer by Sendil Nathan Wednesday, May 11, 2011 12:00 PM
    Wednesday, May 11, 2011 12:00 PM

All replies

  • 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/
    Wednesday, May 11, 2011 10:47 AM
    Answerer
  • 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" }

    }

    • Marked as answer by Sendil Nathan Wednesday, May 11, 2011 12:00 PM
    Wednesday, May 11, 2011 12:00 PM