sqlsever--Basic Learning--Create a login user & Create a database user & assign Permissions

Title, this article précis-writers the creation of the login user (login) in SQL Sever, the creation of the database user (DBUser), and the assignment of permissions (Grant) to the database user.

The database has a three-layer protection mechanism:

The first layer: login user and login password protection, such as: You have access to the community door;

The second layer: database permissions, such as: The community has a lot of buildings, you only have to go into the designated Wendy permission;

The third layer: Permissions for database operations, such as: After you have entered the specified Wendy, you can only do what is allowed.

The first layer inside, the user login is divided into Windows login with Sqlsever login, Windows login does not say, mainly said Sqlsever login.

--First layer: Login user Creation--you can go into the cell. Use mastergoexec sp_addlogin'Test','123456'--exec Sp_droplogin'Test'--To delete a logged in user--Second tier: Database user Creation--you can go into the house. Use tsmsgoexec sp_grantdbaccess'Test','TestUser01'--exec sp_dropuser'TestUser01'--To Delete a database user--Third Layer: Permission assignment--you can do something in the house that has been approved. Use TsmsgograntSelectOn Students to TestUser01--revokeSelectOn Students to TestUser01--permission recall

OK, now know how to create and delete logged-in users, database users, database permissions. Keep adding something, a logged-on user who needs more than one DB of operational privilege needs to create multiple dbuser, simply say: A login can have more than one dbuser, each dbuser corresponds to a db. This explains why each database has a dbo (DB Owner) under the SA account, so that the SA account can operate all the databases!

Specify db→ security → View users

Continue to introduce the problem: if the login user super, do you want one to give them permission? That's so lame! Sqlsever defines a set of user roles to address these issues, as follows:

Role-Granting and deletion:

code example:

--'db_owner','testUser01' --' db_owner ','testUser01' --Remove db_owner permissions

Code tested, test environment: Win10+sqlsever 2014.

sqlsever--Basic Learning--Create a login user & Create a database user & assign Permissions