create user script

• HildaJ

  SSCarpal Tunnel

  Points: 4516

  More actions

  August 28, 2013 at 2:42 pm

  #277440

  I'd like to have a script that contains all the creation of users and statements to add those users to different roles. I have to restored this particular database and on every restore I loose the security assignment on my users. I could use the user interface to reset those users but it would be much easier to use the script since I have to restore every week.

  I have the following script, but I'd like to assign the dbo as the default schema for each use so I don't end up creating user schemas.

  CREATE LOGIN [DOMAIN\user1] FROM WINDOWS;

  GO

  USE [Database1]

  GO

  CREATE USER "DOMAIN\user1" FOR LOGIN "DOMIAN\user1"

  EXEC sp_addrolemember N'db_datareader', N'DOMAIN\user1"

  EXEC sp_addrolemember N'db_executor', N'DOMAIN\user1"

  The script above works great but when I go to my security for the user on the database I see that a schema "DOMIAN\user1" has been created and assigned to the database user. How can I assign the schema "dbo" to the newly created database user?

  Thank you.

• Sarah Wagner

  Hall of Fame

  Points: 3178

  More actions

  September 4, 2013 at 9:28 am

  #1647377

  WITH DEFAULT_SCHEMA=[dbo]

  Users based on logins in master

  CREATE USER user_name

  [

  { FOR | FROM } LOGIN login_name

  ]

  [ WITH DEFAULT_SCHEMA = schema_name ]

  [ ; ]

  http://technet.microsoft.com/en-us/library/ms173463.aspx