Create user roles and authorizations in SQL Server

Reference documents

Http://database.51cto.com/art/201009/224075.htm

Body

To successfully access data in a SQL Server database, we need two aspects of authorization:

1. Get permission to connect to the SQL Server server;
2. The right to access data in a particular database (SELECT, Update, delete, CREATE TABLE ...) ）。

Suppose we are going to set up a DBA database account to manage database MyDB.

1. Start the login account at the SQL Server server level (create login)

--Creating login account (Create login) Create login DBA with password= ' [email protected] ', Default_database=mydb

Login account Name: "DBA", login password: [email protected] ", the default connection to the database:" MyDB ". At this point, the DBA account can connect to the SQL Server server. However, it is not possible to access objects in the database at this time (strictly speaking, the DBA account defaults to the guest database user identity and can access the database objects that the guest can access).

To enable the DBA account to access the objects it needs in the MyDB database, you need to establish a "database user" in the database mydb, give this "database user" some access rights, and map the login account "DBA" to this "database user". In practice, the name "database user" is the same as the name of "login account", i.e. "DBA". Creating a database user and establishing a mapping relationship can be done in just one step:

2. Creating a database User (create user):

-Create a database user for the login account, and under user in security in the MyDB database, you can find the newly created dbacreate User DBA for login DBA with default_schema=dbo

and specifies that the default schema for the database user "DBA" is "dbo". This means that the user "DBA" executes "SELECT * from T" and actually executes "select * from DBO.T".

3. Give the database user "DBA" permission by joining the database role:

--By adding a database role, give the database user "db_owner" permission exec sp_addrolemember ' db_owner ', ' DBA '

At this point, the DBA has full control over the objects in the database MyDB.

If you want the SQL Server login account "DBA" To access multiple databases, such as MYDB2. You can have the SA execute the following statement:

--Let SQL Server login account "DBA" Access multiple databases use Mydb2gocreate user DBA for login dba with default_schema=dbogoexec sp_addrolemember ' db _owner ', ' DBA ' Go

At this point, the DBA can have two database mydb, MYDB2 administrative rights!

Complete code exampleView CodeUse stored procedures to complete user creation

The following example describes how to use stored procedures to create roles in SQL Server, rebuild logins, and how to authorize logins.

The/*--example demonstrates that an example creates a role in the database InsideTSQL2008 that has all the permissions for the table Hr.employees, the SELECT permission that owns the table Sales.orders, and then creates a login r_test, and then the database I A user account is created in nsideTSQL2008 for login L_test U_test also adds the user account u_test to the role r_test so that it gets the same permissions as the role r_test through permission inheritance Finally, the user account is denied using the Deny statement u_    Test SELECT permission on the table hr.employees. After this processing, when you log on to an instance of SQL Server using L_test, it only has the SELECT permission for table sales.orders and all the permissions on the table hr.employees out of SELECT. --*/use insidetsql2008--Create role r_testexec sp_addrole ' r_test '--Add login l_test, set password to PWD, default database is Pubsexec sp_addlogin ' l_test ' , ' [email protected] ', ' InsideTSQL2008 '--Add a security account for login L_test in the database pubs u_testexec sp_grantdbaccess ' l_test ', ' u_test '--add u_test as a member of the role r_test exec sp_addrolemember ' r_test ', ' u_test '-landed with l_test and found that still and tables were not found in SSMs, so the following two statements were executed in error. SELECT * FROM Sales.ordersselect * from HR. employees--grants role r_test to HR. All permissions on the Employees table are grant all on HR. Employees to r_test--the all permission are deprecated and maintained only for compatibility. --it DOES not imply all permissions defined on the Entity.--all permission is no longer recommended and is reserved for compatibility purposes only. It does not imply that all permissions are defined on the entity. -TestThe test can query the table hr.employees, but sales.orders cannot query select * from HR. employees--if you want to reclaim permissions, you can use the following statement. (option to execute) revoke all on HR. Employees from R_test--all permissions are no longer recommended and are reserved for compatibility purposes only. It does not imply that all permissions are defined on the entity. --Grant the role r_test SELECT permission to the Sales.orders table grant Select on Sales.orders to r_test--with L_test login, find can query sales.orders and Hr.employee s two-sheet SELECT * from Sales.ordersselect * from HR. employees--deny security account u_test to HR. The SELECT permission for the Employees table is deny select on HR. Employees to u_test--executes the statement that queries the Hr.employees table again, prompting: the SELECT permission on the object ' Employees ' (Database ' InsideTSQL2008 ', schema ' HR ') is rejected. SELECT * from HR. employees--re-authorizes Grant SELECT on HR. Employees to u_test--query again, you can query the results. SELECT * from HR. Employeesuse insidetsql2008--Delete the security account from the database, Failedexec sp_revokedbaccess ' u_test '--Remove role r_test,failedexec sp_droprole ' R_test '--delete login l_test,successexec sp_droplogin ' l_test '

The difference between revoke and deny

REVOKE: Revoke the permissions granted before

Deny: Denies granting permissions to the security account in the current database and prevents the security account from inheriting permissions through its group or role memberships. For example, UserA's role group has inset permissions, but we deny usera that it does not have insert permissions, then even if UserA to another role group that contains the insert, there is no insert permission, unless the user is shown authorization.

In simple terms, deny is not allowed in the future, revoke is to take back what has been given.

Instance

GRANT insert on TableA to roleagoexec sp_addrolemember Rolea, ' UserA '-user UserA will have TableA insert permission Gorevoke insert on TableA From Rolea--user UserA will not have TableA insert permission, take back permissions gogrant insert on TableA Torolea--re-give Rolea with TableA insert permission go DENY insert on TableA to UserA--although the user UserA Rolea has the TableA insert permission, but UserA itself is denied, so the user UserA will have no TableA insert permissions.

Create user roles and authorizations in SQL Server