SQL statement Operations SQL Server database logins, users, and permissions

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, the newly created DBA can be found under user in security in the MyDB database

Create 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:

--Give database user "db_owner" permission by adding database role

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 the SQL Server login account "DBA" Access to multiple databases

Use mydb2go Create user DBA for login dba with Default_schema=dbogo exec sp_addrolemember ' db_owner ', ' DBA ' Go

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

Complete code example

--Create DATABASE MyDB and MYDB2

--Create a test table in MyDB and MYDB2, which is the DBO schema by default

CREATE TABLE DEPT       (DEPTNO int primary KEY,        dname varchar,        LOC varchar);--Insert Data

INSERT into DEPT values (101, ' ACCOUNTING ', ' NEW YORK '), insert into DEPT values (201, ' the ' ",   ' D Allas '); insert into DEPT values (301, ' SALES ',      ' CHICAGO '); insert into DEPT values (401, ' OPERATIONS ', ' BOSTON ');--View database schema, user's stored procedure

SELECT * FROM Sys.database_principalsselect * to Sys.schemas SELECT * from sys.server_principals--Creating login account (Create login)

Create login DBA with password= ' [email protected] ', default_database=mydb--creates a database user for the login account (create user), The newly created DBA can be found under user in security in the MyDB database

Create user DBA for login DBA with default_schema=dbo--gives database user "db_owner" permissions by joining database roles

exec sp_addrolemember ' db_owner ', ' DBA '

--Let the SQL Server login account "DBA" Access to multiple databases

Use mydb2go Create user DBA for login dba with Default_schema=dbogo exec sp_addrolemember ' db_owner ', ' DBA ' Go

--Disable login account

ALTER LOGIN DBA disable--enable login account

ALTER LOGIN DBA enable--login account renaming

ALTER LOGIN DBA with name=dba_tom--login account Change Password:

ALTER LOGIN DBA with password= ' [email protected] '

--Database user renaming:

Alter user DBA with name=dba_tom--change database defult_schema:

Alter user DBA with default_schema=sales--to delete database users:

Drop user dba--Delete the SQL Server login account:

Drop Login DBA

Use 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.

[SQL]View PlainCopyprint?

2. /*--Example Description
4. The example creates a role in database InsideTSQL2008 that has all the permissions of the table Hr.employees, and the SELECT permission that owns the table Sales.orders r_test
6. A login l_test is then created and a user account is created for the login l_test in the database InsideTSQL2008 u_test
8. The user account U_test is also added to the role r_test so that it gets the same permissions as the role r_test through permission inheritance
10. Finally, using the DENY statement denies the user account U_test SELECT permission on the table Hr.employees .
12. 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.
14. --*/
20. Use InsideTSQL2008
24. --Create a role r_test
26. EXEC sp_addrole ' r_test '
30. --Add login l_test, set password to PWD, default database is pubs
32. EXEC sp_addlogin ' l_test ',' [email protected] ',' InsideTSQL2008 '
36. --Add a security account for login L_test in the database pubs u_test
38. EXEC sp_grantdbaccess ' l_test ',' u_test '
42. --Add u_test as a member of role r_test
44. EXEC sp_addrolemember ' r_test ',' u_test '
50. --Logged in with l_test, found that the still and table cannot be found in SSMs, so the following two statements were executed in error.
52. SELECT * FROM sales.orders
54. SELECT * FROM HR. Employees
58. --Grant the role r_test to HR. Employees all permissions for a table
60. GRANT all on HR. Employees to r_test
62. --the all permission are deprecated and maintained only for compatibility.
64. --it DOES not imply all permissions defined on the entity.
66. --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.
70. --The test can query the table hr.employees, but sales.orders cannot query
72. SELECT * FROM HR. Employees
78. --If you want to reclaim permissions, you can use the following statement. (option to execute)
80. Revoke all on HR. Employees from r_test
82. --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.
88. --Grant the role R_test SELECT permission to the Sales.orders table
90. GRANT SELECT on sales.orders to r_test
94. --with L_test landing, found can query sales.orders and hr.employees two sheets
96. SELECT * FROM sales.orders
98. SELECT * FROM HR. Employees
102. --Deny security account u_test to HR. SELECT permissions for the Employees table
104. DENY SELECT on HR. Employees to u_test
108. --Execute the Query hr.employees table again, prompting: the SELECT permission for object ' Employees ' (Database ' InsideTSQL2008 ', schema ' HR ') is rejected.
110. SELECT * FROM HR. Employees
114. --Re-authorization
116. GRANT SELECT on HR. Employees to u_test
120. --Query again, you can query the results.
122. SELECT * FROM HR. Employees
128. Use InsideTSQL2008
130. --Delete the security account from the database, failed
132. EXEC sp_revokedbaccess ' u_test '
134. --Delete Role r_test,failed
136. EXEC sp_droprole ' r_test '
138. --Delete Login l_test,success
140. EXEC 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 [SQL]View PlainCopyprint?

2. GRANT INSERT on TableA to Rolea
4. GO
6. EXEC sp_addrolemember Rolea, ' UserA ' --user UserA will have TableA insert permission
8. GO
12. REVOKE Insert on TableA from Rolea --user UserA will have no TableA INSERT permission, revoke permissions
14. GO
18. GRANT Insert on TableA Torolea --re-Rolea with TableA insert permission
20. GO
24. Deny insert on TableA to UserA --although the user UserA Rolea has TableA INSERT permission, UserA itself is denied,  Therefore, user UserA will not have TableA insert permission.
26. Excerpt from: http://blog.csdn.net/a497785609/article/details/47686659

SQL statement Operations SQL Server database logins, users, and permissions