- 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.
- Go to YourServer->Security->Login
- Right click, choose New Login
- Choose a Login Name
- Choose “SQL Server authentication”
- Write password twice
- Check “Enforce password policy” (highly recommended)
- Check “Enforce password expiration” (recommended)
- Check “User must change password at next login” (up to you)
- Choose a “Default database” from the list
- Choose a “Default language” from the list
- Now go to “Server Roles” page, in the same window
- Choose “Public”
- Now go to “User mappings” page, in the same window
- Check “Map” on the database(s) you want this “Login” to have access
- User column will be the same as “Login Name”
- Choose a default schema for this “Login” in this database
- For full permissions, choose “dbo” schema.
- In the same page, below, you’ll be presented with a “Database role membership for:” list for the database you choosed previously.
- Check all options, except “db_denydatareader” and “db_denydatawriter”
- 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.