To access a SQL Server database using SQL Server authentication, SQL Server must be configured for SQL authentication and you must create a login account.
To verify the SQL Server authentication mode
1. Start SQL Server Enterprise Manager.
2. Locate the SQL Server instance within the SQL Server group.
3. Right-click your SQL Server and click Properties.
4. Click the Security tab and make sure that authentication is set to SQL Server and Windows.
To create a new SQL Server login
1. Expand the Security folder in the SQL Server Enterprise Manager, right-click Logins, and click New Login.
2. Select the SQL Server Authentication option for Authentication.
3. Enter a login name and strong password. You should use a combination of letters, numbers, and non-alphanumeric characters for the password and use at least 8 characters total.
SQL Server can now authenticate the new login, but the login has no permission to access any of the user-defined databases.
To grant database access
1. Locate the Users folder for the database you want to grant access to, right-click, and click New Database User.
2. Select the Login name from the drop-down list and click OK. The database user name defaults to the same name.
3. Select Database Roles, right-click and then click New Database Role.
Avoid granting permissions to individual database users because the database user name may change and/or additional users may require the same permissions. Instead, grant permissions to database roles.
4. Enter a role name and select the Database Role type as Standard Role. Click the Add button, add the new user you created, and click OK.
5. Select the new role, right-click, and then click Properties.
6. Click Permissions. Locate the relevant stored procedures, and grant the EXEC (execute) permission to these objects.
By default, a new database role has no permission to read the base tables or views in the database and cannot execute stored procedures. It is best practice to grant permissions to stored procedures and views because then you can control what users can read and update.
As an alternative to using Enterprise Manager, you can run the following database script to perform the steps outlined above.
-- Create a new SQL Server Login
exec sp_addlogin @loginame = 'MyUser', @passwd = 'P@ssw0rd'
-- Grant the SQL login access to your database.
-- Create a database user called WebAppUser to which the login is associated
exec sp_grantdbaccess 'MyUser'
-- Create a user-defined database role.
exec sp_addrole 'WebAppUserRole'
-- Add the database user to the new database role.
exec sp_addrolemember 'WebAppUserRole', 'MyUser'
-- Grant the role execute permissions on the stored procedure called sprocname
grant execute on sprocname to WebAppUserRole
Please see the following link for more info: http://msdn.microsoft.com/en-us/library/ms998300.aspx#paght000010_step3