Question and Answers in Sql Server - Part 19

In This post Let we see some important question and answers in Sql Server, which is a continuation of previous post.

69. Create a User in SQL SERVER?

          Users can be created in Server level as well as database level. Now we see Steps to create the new user in server level.

1.     Right click the Security and click New and Select Login.

2.     Now a screen launches and select in General tab.

3.     Give the name for the Login Name

4.     Select Sql Server authentication and give password and new password.

5.     Uncheck the “user must change password at next login.”

6.     Select the Default Database from the database list.

7.     Now select the Server Roles from the left pane.

Server roles are the roles assign to the user to allow the user to do the actions in the server level based on the privileges,

Bulkadmin : allow user to execute the bulk insert statement in DB.

dbcreator : allow user to create the DB

diskadmin: allow user to do the manage disk files.

process admin: allow user to kill the process

public : every user is mapped to public user.

securityadmin : allow user to do the action of grant and deny permission

serveradmin: allow user to do the server wide configuration

setupadmin: allow user to do the setup like linked server

sysadmin: allow user to do all actions in server

8.     Now select the all list from the server roles

9.     Select the “User Mapping” from the left pane. In that we can map a user access to the database and schema.

10. Map the Database and see now and list of database role will seen under the map database panel.

11. Database Role Membership Which gives or specifies the roles to the user for that particular database

Public: this is the default role mapped to the user in db.

db_accessadmin: can able to add or remove access for windows login users.

db_backupoperator: can able to backup the db

db_datareader: can able to do read the data

db_datawriter: can able to do all add, delete and modify the data

db_ddladmin: can able to do all data definition language

db_owner: can able to do configuration and maintenance

db_denydatareader: can’t able to read data.

db_denydatawriter: can’t able to add,delete and modify data.

db_securityadmin: able to add role and manage permissions.

12. Now select the db_owner,db_datareader,db_datawriter,db_ddladmin, public

13. Now select the Securables  in the left pane and click search and add the object and give the give the permission on the list.

14. Now select the status in the left pane and select the Grant in the permission to connect to database engine and select enabled in login.