Setup user account for database in SQL 2008 R2

  1. First, access SQL Server using SQL Server Management Studio 2008 R2, as “sa” user account or Windows administrator, which has full permission over the database server.
  2. Go to YourServer->Security->Login
  3. Right click, choose New Login
  4. Choose a Login Name
  5. Choose “SQL Server authentication”
  6. Write password twice
  7. Check “Enforce password policy” (highly recommended)
  8. Check “Enforce password expiration” (recommended)
  9. Check “User must change password at next login” (up to you)
  10. Choose a “Default database” from the list
  11. Choose a “Default language” from the list
  12. Now go to “Server Roles” page, in the same window
  13. Choose “Public”
  14. Now go to “User mappings” page, in the same window
  15. Check “Map” on the database(s) you want this “Login” to have access
  16. User column will be the same as “Login Name”
  17. Choose a default schema for this “Login” in this database
  18. For full permissions, choose “dbo” schema.
  19. In the same page, below, you’ll be presented with a “Database role membership for:” list for the database you choosed previously.
  20. Check all options, except “db_denydatareader” and “db_denydatawriter”
  21. Press “OK”

Now, to prove everything is OK, close SQL Server Management Studio and try to login using the newly created user account.

You should be able to create, delete, modify any table, store procedure, etc.

References:

Setup user account and database in SQL Server 2005

 

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: