MSSQL Security Logins

Hello Everyone,

This is another day full of new insights and learning on how to manage users in MSSQL. On this topic we will discuss how to grant user access to Database/s.

Below is the step by step procedure:

Step 1. First you will need to login to SQL Server Management Studio: Define which DB Server you are connecting to.

Step 2. Go to Logins. Expand Security of the DB Server.

Step 3. Create New Login. Right click "Logins" and click "New Login..."

Step 4. On the "General" page of the Login - New window, you can either search for a windows authenticated user or you can manually create a SQL authenticated user with your preferred username and password:

Note: For security reasons, just for this topic, you should define the "Default database" to a user Database (ex: TestDataBase). By default, it is always set to "master".

Step 5. On the "Select  a page", click Server Roles. By default, "Public" role is always assigned to a SQL Server Login.

Note:
- For Server Level Roles definition, you might want to check this link - http://msdn.microsoft.com/en-us/library/ms188659.aspx

Sample:
- If you want to perform any activity in the SQL Server, GRANT the user sysadmin role. On the other hand, if you wish to GRANT a user with DB role membership only, proceed to Step 6.


Step 6. Go to "User Mapping" page, select and assign a Database to a user then GRANT database role membership to the user.

Note: You can check the link http://msdn.microsoft.com/en-us/library/ms189121.aspx for DB role membership.

Step 7. After modifying the DB role membership, click "OK" button.

Congratulations! You're done creating SQL user.

For any Comments or Suggestions, please feel free!