SQL Server statement new user, authorization to user, delete user instance

Grant Select  on  to Db_user -- Grant TB Table query permissions to db_user users

One, command operation

Use MyDB

GO

--1. New test user

--1.1 Add login user and password

EXEC sp_addlogin N ' Tony ', ' 123 '

--1.2 make it a legitimate user of the current database

EXEC sp_grantdbaccess N ' Tony '

--2. Setting Operation authorizations

--2.1 Grant all permissions to its own database

EXEC sp_addrolemember n ' db_owner ', n ' Tony '

--2.2 The following is the set specific operation permissions

--Grant Tony permission to operate on all user tables

GRANT Select,insert,update,delete to Tony

--Grant Tony Select,update to the specific table

GRANT select,update on TB to Tony

--Grant Tony select,update to specific tables and columns

GRANT select,update on TB (id,col) to Tony

--Prohibit Tony from operating permissions on all user tables

DENY Select,insert,update,delete to Tony

--Forbid Tony select,update to a specific table

DENY select,update on TB to Tony

--Prohibit Tony select,update to specific tables and columns

DENY select,update on TB (id,col) to Tony

--Remove Tony's authorization information for all user tables

REVOKE Select,insert,update,delete to Tony

--Grant Tony access to operations that create tables, views, stored procedures, and so on

GRANT CREATE table,create view,create PROC to Tony

--Prohibit Tony from having permission to create tables, views, stored procedures, and so on

DENY CREATE table,create view,create PROC to Tony

--Remove Tony's authorization information for creating tables, views, stored procedures, and so on

REVOKE CREATE table,create view,create PROC to Tony

GO

--Note: More relevant authorization information refer to the "Database Permissions" column in the following schedule.

--3. To delete a test user

EXEC sp_revokedbaccess N ' Tony '--Remove user access to the database

EXEC sp_droplogin N ' Tony '--Delete logged-in user

GO

Second, manual distribution

First, Demand

In the management of the database process, we often need to control the access of a user to the database, such as only need to give the user access to a table permissions, or even crud permissions, the smaller the granularity can go to a few fields access rights. Writing this article is a description of the operation process.

In fact, this is just a simple SQL Server permissions management, some places do not understand and tell, just hope that some of the newly-introduced children's shoes help, other heroes are: I classroom startled, and then have to laugh. (Holy grail of gambling)

Second, Operation Steps

1. First Enter database-level security-Login name-New login

( )

2. in the General tab, create a login name as shown, and set the default database

( )

3. in the User Mappings tab, as shown in, tick the database you need to set up, and set the "Schema", click "Confirm" button to complete the creation of the user's action

( )

4. Now we can set permissions on the user table in the Testlog database, "table"-"Properties"

( )

5. in the "Permissions" tab, as shown, click "Add"-"Browse"-"Select Object"

( )

6. after clicking on "Confirm" above, we can find the corresponding permission in the list below, if you want to refine the permissions of the column, there is a "column permission" button in the lower right corner to set, click "Confirm" button to complete the settings of these permissions

( )

7. now use the testuser user login database, after landing as shown, now only see a table

( Figure 7: Effects )

Third, Precautions

1. in the 3rd step above, it is important to note that if the corresponding database is not selected, then the testuser will not be found in the Testlog database.

( )

     

     

( Figure 9: Delete testuser user )

4. In the "explicit Permissions" list in step 6th, if you select the "Control" option, setting the Query "column permissions" in the "select" is meaningless and the query is not restricted. If you set "column Permissions", the error message will be displayed under normal circumstances:

( )

5. in the Testlog database "security"-"TestUser"-"Properties"-"securable"-"Add"-"Object type" Here are more about the database level of some object types can be set.

( Figure 11: Other object Types )

SQL Server statement new user, authorization to user, delete user instance