List of logins, users, roles, and Concepts in SQL Server

Reprint:http://www.2cto.com/database/201306/216922.html    database, roles, users, security        Login SQL Server 2008 can also use Windows authentication or SQL Server authentication, which is obviously less secure, regardless of whether   has superuser privileges after entering the database. It is reasonable to do this: for a database, it should be operated and managed by users of different permissions  .          This blog post will be an example of how to set up a database and set up users with different permissions for the database.          Login database management system (open SQL Server database service first)         1, log on with Windows authentication           2, log in with SQL Server authentication, the default login is SA, and if the password is forgotten, you can log in with Windows and enter the   changes. Security, login name, double-click sa-> to set the password.             Second, set up database library           1, database--New database & nbsp;          2, fill in the database name             three, Establish a basic table for database library: Student table student, Books table book, borrowing table borrow. and import a certain amount of data.          Iv. create users.          Here for simplicity, create only two users-student users (Student_user), admin users (Admin_user). Its secondary   student users can only view (select), and the administrator user may consult (select), updating (update), inserting (insert), deleting (delete).          1, first to create a login name, no login specified user               ① student user  [ Sql] use Library  go  create login stu with password= ' 123 ';    use Library  go  create user student_user for login Stu;               ② Admin user  [sql] use library  go  create Login Stu With password= ' 123 ';    use Library  go  create user student_user for login Stu;                      2, create role no role authorization        &N Bsp        ① student roles  [sql] create role student_role;  grant Select on book to Student_role;  grant Select on borrow to Student_role;  grant Select on student to Student_role;                 ② Administrator roles  [sql] create role admin_role;  grAnt Select,update,delete,insert on book to Admin_role;  grant Select,update,delete,insert on borrow to Admin_role;  grant Select,update,delete,insert on student to Admin_role;               3, give roles to users. Student_role Grant Student_user,admin_role Grant Admin_user.                   ①student_role Grant Sthdent_user [sql] exec SP _addrolemember    @rolename = ' student_role ',   @membername = ' student_user ';                   ②admin_role Grant Admin_user [sql] exec sp_ Addrolemember    @rolename = ' admin_role ',   @membername = ' admin_user ';               Five, respectively with Stu and admin login, you can find Stu login only to the basic table query operations, and admin login In addition to   query outside , you can also insert, delete, update. This achieves the beginning of the blog post, the different permissions of the user to do different operations, so that the   database more secure.

Logins, users, roles, concepts at a glance in SQL Server