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

Source: Internet
Author: User

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.   Category:  sql Server 2008

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.