Add a User in SQL Server 2008 R2
Here we are going to create users in SQL Server 2008 R2 using SQL Server Management Studio, and, this user will be used in our application which is based on current SQL Server database. (this new user will be created according to our application case, for your case, there might be slight difference)
There are two types users in SQL Server database: Login user and Database user. Check the following content from Microsoft to know their differences:
A login is a security principal, or an entity that can be authenticated by a secure system. Users need a login to connect to SQL Server. You can create a login based on a Windows principal (such as a domain user or a Windows domain group) or you can create a login that is not based on a Windows principal (such as an SQL Server login). (From here)
A user (Note: it means Database User – by CodeEase) is a database level security principal. Logins must be mapped to a database user to connect to a database. A login can be mapped to different databases as different users but can only be mapped as one user in each database. In a partially contained database, a user can be created that does not have a login. For more information about contained database users, see CREATE USER (Transact-SQL). If the guest user in a database is enabled, a login that is not mapped to a database user can enter the database as the guest user. (from here)
How to add a login user:
1: Choose "SQL Server Management Studio":
2: You will see the connect to server screen, which lets you put an existing login user to connect to database server first.
Normally you can use "Windows Authentication" to connect the database directly without need login information. But please make sure you input correct "Server name" which means the host server that you have the SQL Server database installed;
3: You will see the Object Explorer screen:
Expand Security, and right click Logins, choose New Login…
4: In General tab screen, input your new Login name, and check SQL Server authentication (normally this is for a real database application), and remember check off the "Enforce password policy" which let the database user and login user keep on working when you have a real application which is based on current database.
5: Now we turn to Server Roles tab, In our case, we need this user has system admin right, so we check on sysadmin and public options:
6: now User Mapping, the User Mapping means you need to specify database user to map current login user (please check above content about the difference between login user and database user).
for example, we check on a database in the database list, the user will be automatically filled with the same name as current login name; and, for our case, we checked on master database also;
And, you need to specify what database role membership for the database which you selected for user mapping. normally, we need to check on db_owner, the public option checked on by default. actually, if you do not worry the new login user gets too much rights, you can simply check on all database role membership options, and check on all database mappings to current login user:
7: make sure do not change the default settings in Status tab screen as the following:
the Permission to connect to database engine set to Grant
Login set to Enabled
8: Click OK button, you will see a new login user has been added when you expand the Security node:
Try to use the new user name to login database, and add this user name into your software application (using sql server database connection string) to run your application, if you got any error message, please adjust the options due to your issue.