Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1

    Unanswered: New DBA Needs To Automate Creating LOGIN/ROLE

    I'm a new Jr. DBA and I'm currently being asked several times a day to add new users as follows:

    Code:
    USE [master]
    GO
    
    CREATE LOGIN [ZOO\johnsmith] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
    GO
    
    USE [marketing]
    GO
    
    CREATE USER [ZOO\johnsmith] FOR LOGIN [ZOO\johnsmith]
    GO
    
    USE [marketing]
    GO
    
    ALTER ROLE [agent] ADD MEMBER [ZOO\johnsmith]
    GO
    I'm not seasoned enough to understand what is the best way to simplify this task I run several times per week. It's very very time consuming to launch SSMS and script out new user request as I scripted out above. Should I create a sproc in SSMS or what do you guys recommend if I'm constantly given a list of names to add to SQL Server 2012?

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You can probably make a simple batch script for this.

    warning. This is "air code", and has not been tested.
    Code:
    @echo off
    REM Arguments:
    REM %1 - Username
    REM %2 - Database name
    REM %3 - Role name
    
    REM usage:
    REM batch.bat username databasename rolename
    
    if "%~1" EQU "" goto :error
    if "%~2" EQU "" goto :error
    if "%~3" EQU "" goto :error
    
    sqlcmd /E /S servername /Q"create login [%1] from windows with default_database=[%2]" /b
    if %ERRORLEVEL% neq 0 goto :error
    sqlcmd /E /S servername /d %2 /Q"create user [%1] from login [%1]" /b
    if %ERRORLEVEL% neq 0 goto :error
    sqlcmd /E /S servername /d %2 /Q"alter role [%3] add member [%1]" /b
    if %ERRORLEVEL% neq 0 goto :error
    
    exit /B
    
    :error
    echo "The process failed"
    There are very probably more elegant ways of doing this using powershell, but this should get y ou started. Play around with this. DOS scripting is very finicky, and very limited, but can also be quick and dirty.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •