Thursday, 21 November 2013

SQL SERVER - Notes 10

68. What is Change Tracking ?
       Change Tracking is used to track the changes takes place in the tables with in the database. to read about Change Tracking Click Here to read about Change Tracking

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.

70. what are the various options of recovery model  ?
          Full, Bulk-logged, Simple
          Full : No work is lost, due to failure. It is default recovery model.
          Bulk-logged : Allows high-performance bulk copy operations. Minimum space    
                               is required , we can perform recovery when have taken the     
                                  backup of the database.
          Simple: Allows high-performance bulk copy operations,  we can perform  
                        recovery when we have taken the backup,advantage of using this is  
                        minimumspace requirement.

71. How to back-up database?

1.     Right click the database,Select Tasks in that select Back up database.
2.     Select the General in the left pane.
3.     Select the database to back up.
4.     Select a Back up type.
Full: Used for database , files, and filegroups.
Differential : Used for capture all data’s that has changed since the last full database backup.
Transaction log: Capture the modifications to the database, provide the history of the transaction takes place in the database.
Select the Full option.
5.     Select “Database” in back up component group.
6.     Give the name of the backup database.
7.     Select the Disk in the destination back up type and click ok.

I hope from this series of questions and explanation will give a basics and intermediate things in SQL SERVER. This articles make you to understand the about SQLSERVER.

No comments:

Post a Comment