SQL Server user creation

• bloophone

  SSC Rookie

  Points: 35

  More actions

  August 26, 2004 at 10:24 am

  #89128

  Hi,

  I would like to know if it's possible to create a user with a script having : DB_name and sa_password.

  Can my T-SQL scripts be executed automaticly just by clicking on a setup binary ?

  The purpose of all of this work is to enable the user to have only a setup binary, which creates database,user connection,triggers and jobs on server using sql scripts.

   

  Thanks   

   

    

• sa24

  SSCrazy Eights

  Points: 8027

  More actions

  August 26, 2004 at 10:34 am

  #520756

  Below is from BOL.

  sp_addlogin [ @loginame = ] 'login'

      [ , [ @passwd = ] 'password' ]

      [ , [ @defdb = ] 'database' ]

      [ , [ @deflanguage = ] 'language' ]

      [ , [ @sid = ] sid ]

      [ , [ @encryptopt = ] 'encryption_option' ]

• george@esser.ca

  SSC Enthusiast

  Points: 184

  More actions

  August 29, 2004 at 7:10 pm

  #521007

  Hi - You have to do more than just add a user to a login; they need to have access granted, etc.

   

  DECLARE @User      varchar(255)

        , @Pwd       varchar(255)

        , @DefaultDB varchar(255)

      SET @User      = 'George'

      SET @Pwd       = 'password'

      SET @DefaultDB = 'SID_DB'

  EXEC sp_addlogin @User

  EXEC sp_addsrvrolemember @User , 'sysadmin'

  EXEC sp_defaultdb @User, @DefaultDB

  EXEC sp_password NULL, @Pwd, @User

   

  --If you are using roles, use this to add them to it

  EXEC sp_addrolemember 'DISPATCHER','George'

   

  you may want to do other things too like

  Exec sp_defaultlanguage 'George', 'french'

   

  --To get rid of my test login

  EXEC sp_droplogin 'George'

   

  Hoped this helped

  Have fun

   

  George