In this blog, I will discuss how to give SQL database access permission to a specific user.
Step 1: Login your SQL server as an Administrator user
Step 2: Click on the Username whom you want to give a specific database access permission
Click right button on username -> select Properties
Step 3: Here you can see a Login Properties dialog box
I will discuss here Server roles, User Mapping, and status
Server roles: Here you can give a bunch of admin power to a user based on nature of user activities.
sysadmin: Members of the sysadmin fixed server role can perform any activity on the server.
serveradmin: Members of the serveradmin fixed server role can change server-wide configuration options and shut down the server.
securityadmin: Members of the securityadmin fixed server role manage logins and their properties. The can grant, revoke, deny server level permission and database level permission if they have permission to access database as well.
You can see more from : https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles
User Mapping: User Mapping is that grant a user to access a specific database based on nature of user activities
Here, I am giving permission user momo9 to access testdb database instance and allowing some specific roles such as db_ddladmin, db_datawriter, db_datareader etc.
You can set your user permission based on what activities that user wants to do on that specific database. More on: https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles
Step 4: Status
here you can see permission to database permission “Grant” or “Deny” and Login “Enable ” or Disable”
Step 5: As I have given momo9 user admin power. That specific user can now create a table, insert the value and execute select statement without any error.
Thank you 🙂