SRE文档

深浅模式

1.sqlServer创建用户及分配权限 ​

官方文档，https://docs.microsoft.com/zh-CN/sql/sql-server/?view=sql-server-2017

1.1架构主体 ​

数据库主体 ​

--数据库主体 【创建数据库用户的信息会写入该主体中】

sql
use master
select * from sys.database_principals
use master
select * from sys.database_principals

数据库架构 ​

--创建角色权限的信息会写入该主体中

sql
use master
select * from sys.schemas
use master
select * from sys.schemas

服务器主体 ​

--创建登录用户的信息会写入该主体中

sql
use master
select * from sys.server_principals
use master
select * from sys.server_principals

1.2用户级别 ​

1.2.1服务器级别 ​

官方文档，https://docs.microsoft.com/zh-cn/sql/relational-databases/security/authentication-access/server-level-roles?view=sql-server-2017

1.2.1.1服务器级用户（即：登录名） ​

服务器级别下的登录名是指：有权限登录到某个SQL Server服务器的用户。

例如：超级管理员的登录名是sa；

登录名具体位置在： 数据库——>安全性——>登录名

1.2.1.2服务器级角色 ​

服务器角色具体位置在： 数据库——>安全性——>服务器角色

服务器级角色是指：为帮助您管理服务器上的权限，SQL Server 提供了若干角色，这些角色是用于对其他主体进行分组的安全主体。 服务器级角色的权限作用域为服务器范围。SQL Server 提供了九种固定服务器角色， 无法更改授予固定服务器角色的权限，这9组角色分别如下：

服务器级的固定角色说明
sysadminsysadmin 固定服务器角色的成员可以在服务器上执行任何活动。
serveradminserveradmin 固定服务器角色的成员可以更改服务器范围的配置选项和关闭服务器。
securityadminsecurityadmin 固定服务器角色的成员可以管理登录名及其属性。 他们可以 GRANT、DENY 和 REVOKE 服务器级权限。 他们还可以 GRANT、DENY 和 REVOKE 数据库级权限（如果他们具有数据库的访问权限）。 此外，他们还可以重置登录SQL Server密码。 **重要：**通过向用户授予访问权限数据库引擎配置用户权限的能力允许安全管理员分配大多数服务器权限。 securityadmin 角色应视为与 sysadmin 角色等效。
processadminprocessadmin 固定服务器角色的成员可以结束在实例中运行的进程SQL Server。
setupadminsetupadmin 固定服务器角色的成员可以使用 Transact-SQL 服务器。 (Management Studio.)
bulkadminbulkadmin 固定服务器角色的成员可以运行 语句。 Linux 上的 SQL Server 不支持 bulkadmin 角色或管理大容量操作权限。 只有 sysadmin 才能对 Linux 上的 SQL Server 执行批量插入。
diskadmindiskadmin 固定服务器角色用于管理磁盘文件。
dbcreatordbcreator 固定服务器角色的成员可以创建、更改、删除和还原任何数据库。
public登录SQL Server属于公共服务器角色。 如果未向某个服务器主体授予或拒绝对某个安全对象的特定权限，该用户将继承授予该对象的 public 角色的权限。 只有在希望所有用户都能使用对象时，才在对象上分配 Public 权限。 你无法更改具有 Public 角色的成员身份。 注意：public 与其他角色的实现方式不同，可通过 public 固定服务器角色授予、拒绝或撤销权限。
固定服务器角色的权限 ​

每个固定服务器角色都被分配了特定的权限。 下图显示了分配给服务器角色的权限。

  • 查看服务器权限
sql
use master
SELECT * FROM sys.fn_builtin_permissions('SERVER') ORDER BY permission_name;
use master
SELECT * FROM sys.fn_builtin_permissions('SERVER') ORDER BY permission_name;

1.2.2数据库级别 ​

1.2.2.1数据库级用户 ​

数据库级用户是指：有权限能操作（比如：增删查改等权限）数据库的用户。

用户具体位置在 数据库——>某个具体库——>安全性——>用户

1.2.2.2数据库级角色 ​

官方文档，https://docs.microsoft.com/zh-cn/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-2017

通过使用角色，不必针对各个用户单独维护权限.

存在两种类型的数据库级角色：数据库中预定义的“固定数据库角色”和可以创建的“用户定义的数据库角色”。

固定数据库角色是在数据库级别定义的，并且存在于每个数据库中。 db_owner 数据库角色的成员可以管理固定数据库角色成员身份。 msdb 数据库中还有一些特殊用途的数据库角色

SQL Server 支持四种类型的角色

  • 固定服务器角色
  • 用户定义的服务器角色
  • 固定的数据库角色
  • 用户定义的数据库角色
固定数据库角色 ​

下表显示了固定数据库角色及其能够执行的操作。 所有数据库中都有这些角色。 无法更改分配给固定数据库角色的权限，“公共”数据库角色除外

固定数据库角色名说明
db_ownerdb_owner 固定数据库角色的成员可以执行数据库的所有配置和维护活动，还可以 drop SQL Server 中的数据库。 （在 SQL 数据库 和 Synapse Analytics 中，某些维护活动需要服务器级别权限，并且不能由 db_owners 执行。）
db_securityadmindb_securityadmin 固定数据库角色的成员可以仅修改自定义角色的角色成员资格和管理权限。 此角色的成员可能会提升其权限，应监视其操作。
db_accessadmindb_accessadmin 固定数据库角色的成员可以为 Windows 登录名、Windows 组和 SQL Server 登录名添加或删除数据库访问权限。
db_backupoperatordb_backupoperator 固定数据库角色的成员可以备份数据库。
db_ddladmindb_ddladmin 固定数据库角色的成员可以在数据库中运行任何数据定义语言 (DDL) 命令。
db_datawriterdb_datawriter 固定数据库角色的成员可以在所有用户表中添加、删除或更改数据。
db_datareaderdb_datareader 固定数据库角色的成员可以从所有用户表和视图中读取所有数据。 用户对象可能存在于除 sys 和 INFORMATION_SCHEMA 以外的任何架构中 。
db_denydatawriterdb_denydatawriter 固定数据库角色的成员不能添加、修改或删除数据库内用户表中的任何数据。
db_denydatareaderdb_denydatareader 固定数据库角色的成员不能读取数据库内用户表和视图中的任何数据。
  • 下图显示了分配给固定数据库角色的权限

  • 将用户添加到数据库级角色 ​

sql
use master
ALTER ROLE db_datareader
	ADD MEMBER username;  
GO
use master
ALTER ROLE db_datareader
	ADD MEMBER username;  
GO

  • 列出作为数据库级别角色成员的所有数据库主体 ​

下面的语句将返回任何数据库角色的所有成员

sql
use DB_name
SELECT    roles.principal_id                            AS RolePrincipalID
    ,    roles.name                                    AS RolePrincipalName
    ,    database_role_members.member_principal_id    AS MemberPrincipalID
    ,    members.name                                AS MemberPrincipalName
FROM sys.database_role_members AS database_role_members  
JOIN sys.database_principals AS roles  
    ON database_role_members.role_principal_id = roles.principal_id  
JOIN sys.database_principals AS members  
    ON database_role_members.member_principal_id = members.principal_id;
use DB_name
SELECT    roles.principal_id                            AS RolePrincipalID
    ,    roles.name                                    AS RolePrincipalName
    ,    database_role_members.member_principal_id    AS MemberPrincipalID
    ,    members.name                                AS MemberPrincipalName
FROM sys.database_role_members AS database_role_members  
JOIN sys.database_principals AS roles  
    ON database_role_members.role_principal_id = roles.principal_id  
JOIN sys.database_principals AS members  
    ON database_role_members.member_principal_id = members.principal_id;

1.2权限层次结构 ​

1.3查看当前的用户连接 ​

非系统连接

sql
---使用SQL语句查看当前连接的用户
select spid,db_name(dbid) as DBname,login_time ,last_batch ,status ,hostname ,program_name ,loginame   
from sys.sysprocesses 
where spid >50
and loginame <> 'DESKTOP-ABCD\Administrator'
--and dbid in (select dbid from master.dbo.sysdatabases where name ='要查询的数据库名称')
order by last_batch desc
---使用SQL语句查看当前连接的用户
select spid,db_name(dbid) as DBname,login_time ,last_batch ,status ,hostname ,program_name ,loginame   
from sys.sysprocesses 
where spid >50
and loginame <> 'DESKTOP-ABCD\Administrator'
--and dbid in (select dbid from master.dbo.sysdatabases where name ='要查询的数据库名称')
order by last_batch desc

1.3创建用户 ​

官方文档，https://docs.microsoft.com/zh-cn/sql/t-sql/statements/create-user-transact-sql?view=sql-server-2017

https://docs.microsoft.com/zh-cn/sql/t-sql/lesson-2-configuring-permissions-on-database-objects?view=sql-server-2017

https://docs.microsoft.com/zh-cn/sql/t-sql/statements/create-login-transact-sql?view=sql-server-2017

1.3.0 用户类型 ​

1.3.1创建登录用户 ​

  • 语法
sql
-- Syntax for SQL Server
CREATE LOGIN login_name { WITH <option_list1> | FROM <sources> }

<option_list1> ::=
    PASSWORD = { 'password' | hashed_password HASHED } [ MUST_CHANGE ]
    [ , <option_list2> [ ,... ] ]

<option_list2> ::=
    SID = sid
    | DEFAULT_DATABASE = database
    | DEFAULT_LANGUAGE = language
    | CHECK_EXPIRATION = { ON | OFF}
    | CHECK_POLICY = { ON | OFF}
    | CREDENTIAL = credential_name

<sources> ::=
    WINDOWS [ WITH <windows_options>[ ,... ] ]
    | CERTIFICATE certname
    | ASYMMETRIC KEY asym_key_name

<windows_options> ::=
    DEFAULT_DATABASE = database
    | DEFAULT_LANGUAGE = language
-- Syntax for SQL Server
CREATE LOGIN login_name { WITH <option_list1> | FROM <sources> }

<option_list1> ::=
    PASSWORD = { 'password' | hashed_password HASHED } [ MUST_CHANGE ]
    [ , <option_list2> [ ,... ] ]

<option_list2> ::=
    SID = sid
    | DEFAULT_DATABASE = database
    | DEFAULT_LANGUAGE = language
    | CHECK_EXPIRATION = { ON | OFF}
    | CHECK_POLICY = { ON | OFF}
    | CREDENTIAL = credential_name

<sources> ::=
    WINDOWS [ WITH <windows_options>[ ,... ] ]
    | CERTIFICATE certname
    | ASYMMETRIC KEY asym_key_name

<windows_options> ::=
    DEFAULT_DATABASE = database
    | DEFAULT_LANGUAGE = language
sql
-- Syntax for SQL Server, Azure SQL Database, and Azure SQL Managed Instance
  
-- Syntax Users based on logins in master  
CREATE USER user_name   
    [   
        { FOR | FROM } LOGIN login_name   
    ]  
    [ WITH <limited_options_list> [ ,... ] ]   
[ ; ]  
  
-- Users that authenticate at the database  
CREATE USER   
    {  
      windows_principal [ WITH <options_list> [ ,... ] ]  
  
    | user_name WITH PASSWORD = 'password' [ , <options_list> [ ,... ]   
    | Azure_Active_Directory_principal FROM EXTERNAL PROVIDER
    }  
  
 [ ; ]  
  
-- Users based on Windows principals that connect through Windows group logins  
CREATE USER   
    {   
          windows_principal [ { FOR | FROM } LOGIN windows_principal ]  
        | user_name { FOR | FROM } LOGIN windows_principal  
}  
    [ WITH <limited_options_list> [ ,... ] ]   
[ ; ]  
  
-- Users that cannot authenticate   
CREATE USER user_name   
    {  
         WITHOUT LOGIN [ WITH <limited_options_list> [ ,... ] ]  
       | { FOR | FROM } CERTIFICATE cert_name   
       | { FOR | FROM } ASYMMETRIC KEY asym_key_name   
    }  
 [ ; ]  
  
<options_list> ::=  
      DEFAULT_SCHEMA = schema_name  
    | DEFAULT_LANGUAGE = { NONE | lcid | language name | language alias }  
    | SID = sid   
    | ALLOW_ENCRYPTED_VALUE_MODIFICATIONS = [ ON | OFF ] ]  
  
<limited_options_list> ::=  
      DEFAULT_SCHEMA = schema_name ]   
    | ALLOW_ENCRYPTED_VALUE_MODIFICATIONS = [ ON | OFF ] ]  
  
-- SQL Database syntax when connected to a federation member  
CREATE USER user_name  
[;]

-- Syntax for users based on Azure AD logins for Azure SQL Managed Instance
CREATE USER user_name   
    [   { FOR | FROM } LOGIN login_name  ]  
    | FROM EXTERNAL PROVIDER
    [ WITH <limited_options_list> [ ,... ] ]   
[ ; ]  

<limited_options_list> ::=  
      DEFAULT_SCHEMA = schema_name 
    | DEFAULT_LANGUAGE = { NONE | lcid | language name | language alias }   
    | ALLOW_ENCRYPTED_VALUE_MODIFICATIONS = [ ON | OFF ] ]
-- Syntax for SQL Server, Azure SQL Database, and Azure SQL Managed Instance
  
-- Syntax Users based on logins in master  
CREATE USER user_name   
    [   
        { FOR | FROM } LOGIN login_name   
    ]  
    [ WITH <limited_options_list> [ ,... ] ]   
[ ; ]  
  
-- Users that authenticate at the database  
CREATE USER   
    {  
      windows_principal [ WITH <options_list> [ ,... ] ]  
  
    | user_name WITH PASSWORD = 'password' [ , <options_list> [ ,... ]   
    | Azure_Active_Directory_principal FROM EXTERNAL PROVIDER
    }  
  
 [ ; ]  
  
-- Users based on Windows principals that connect through Windows group logins  
CREATE USER   
    {   
          windows_principal [ { FOR | FROM } LOGIN windows_principal ]  
        | user_name { FOR | FROM } LOGIN windows_principal  
}  
    [ WITH <limited_options_list> [ ,... ] ]   
[ ; ]  
  
-- Users that cannot authenticate   
CREATE USER user_name   
    {  
         WITHOUT LOGIN [ WITH <limited_options_list> [ ,... ] ]  
       | { FOR | FROM } CERTIFICATE cert_name   
       | { FOR | FROM } ASYMMETRIC KEY asym_key_name   
    }  
 [ ; ]  
  
<options_list> ::=  
      DEFAULT_SCHEMA = schema_name  
    | DEFAULT_LANGUAGE = { NONE | lcid | language name | language alias }  
    | SID = sid   
    | ALLOW_ENCRYPTED_VALUE_MODIFICATIONS = [ ON | OFF ] ]  
  
<limited_options_list> ::=  
      DEFAULT_SCHEMA = schema_name ]   
    | ALLOW_ENCRYPTED_VALUE_MODIFICATIONS = [ ON | OFF ] ]  
  
-- SQL Database syntax when connected to a federation member  
CREATE USER user_name  
[;]

-- Syntax for users based on Azure AD logins for Azure SQL Managed Instance
CREATE USER user_name   
    [   { FOR | FROM } LOGIN login_name  ]  
    | FROM EXTERNAL PROVIDER
    [ WITH <limited_options_list> [ ,... ] ]   
[ ; ]  

<limited_options_list> ::=  
      DEFAULT_SCHEMA = schema_name 
    | DEFAULT_LANGUAGE = { NONE | lcid | language name | language alias }   
    | ALLOW_ENCRYPTED_VALUE_MODIFICATIONS = [ ON | OFF ] ]
  • 创建登陆名
sql
use master
CREATE login LoginUserName WITH password = '340$Uuxwp7Mcxo7Khy', default_database= database_name
use master
CREATE login LoginUserName WITH password = '340$Uuxwp7Mcxo7Khy', default_database= database_name

==该句执行，数据会保存在服务器主体sys.server_principals中==

  • 创建数据库用户并与登录用户建立连接
sql
#指定默认数据库【为了保持创建数据库用户与创建登录用户默认数据库一致,当前sql所在数据库可能不是创建登录用户的默认数据库。若一致，则可以不用执行

#DatabaseUserName]和[LoginUserName]名最好保持一致，方便确定以后登录和查看对应关系

USE DatabaseUserName
CREATE USER [DatabaseUserName] FOR login [LoginUserName] WITH default_schema = dbo
#指定默认数据库【为了保持创建数据库用户与创建登录用户默认数据库一致,当前sql所在数据库可能不是创建登录用户的默认数据库。若一致，则可以不用执行

#DatabaseUserName]和[LoginUserName]名最好保持一致，方便确定以后登录和查看对应关系

USE DatabaseUserName
CREATE USER [DatabaseUserName] FOR login [LoginUserName] WITH default_schema = dbo

==该句执行，数据会保存在数据库主体sys.database_principals中==

1.3.2删除登陆用户 ​

  • 语法
sql
--声明数据库引用
use database_name;
go
 
--判断是否存在用户，如果存在则删除
if exists(select * from sys.sql_logins where name=login_name)
drop login login_name;
go

-- 或者
EXEC sp_revokedbaccess "[LoginUserName]"
--声明数据库引用
use database_name;
go
 
--判断是否存在用户，如果存在则删除
if exists(select * from sys.sql_logins where name=login_name)
drop login login_name;
go

-- 或者
EXEC sp_revokedbaccess "[LoginUserName]"

1.3.3禁用登陆帐户 ​

sql
alter login login_usernmae disable
alter login login_usernmae disable

1.3.4启用登陆帐户 ​

sql
alter login login_usernmae enable
alter login login_usernmae enable

1.3.5登陆帐户改名 ​

sql
alter login old_login_username with name=new_login_username
alter login old_login_username with name=new_login_username

1.3.6登陆帐户改密码 ​

sql
alter login login_username with password='newpassword'
alter login login_username with password='newpassword'

1.3.7检查有没有登录权限 ​

sql
select is_disabled
,loginproperty(name,'Isexpired') is_expired
,loginproperty(name,'Islocked') is_locked
,* 
from sys.server_principals
where name = 'Login_username' -- Login_username 根据自己名字
select is_disabled
,loginproperty(name,'Isexpired') is_expired
,loginproperty(name,'Islocked') is_locked
,* 
from sys.server_principals
where name = 'Login_username' -- Login_username 根据自己名字

1.4 数据库用户名 ​

1.4.0创建数据库用户 ​

sql
create user data_usernmae for login login_username with default_schema=dbo
create user data_usernmae for login login_username with default_schema=dbo

1.4.1数据库用户改名 ​

sql
-- 语法
alter user database_username with name=dba_tom
-- 语法
alter user database_username with name=dba_tom
sql
-- 将数据库用户 testuser 的名称更改为 testuser1

ALTER USER hantestuser WITH NAME = hantestuser1;
GO
-- 将数据库用户 testuser 的名称更改为 testuser1

ALTER USER hantestuser WITH NAME = hantestuser1;
GO

1.4.2删除数据库用户 ​

  • 语法
sql
-- Syntax for SQL Server and Azure SQL Database  
  
DROP USER [ IF EXISTS ] user_name


-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse  
  
DROP USER user_name
-- Syntax for SQL Server and Azure SQL Database  
  
DROP USER [ IF EXISTS ] user_name


-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse  
  
DROP USER user_name

1.4查看用户 ​

sql
-- 查看所有
SELECT * FROM sys.server_principals

-- 查看指定登陆用户
SELECT * FROM sys.server_principals WHERE name LIKE '%han%';

-- 或者
sp_who

-- 查看该登陆名下的所有连接
sp_who + 登录名
-- 查看所有
SELECT * FROM sys.server_principals

-- 查看指定登陆用户
SELECT * FROM sys.server_principals WHERE name LIKE '%han%';

-- 或者
sp_who

-- 查看该登陆名下的所有连接
sp_who + 登录名

查看所有登陆用户名 ​

sql
select  convert(varbinary(255), isnull(password, '')) passwd,sid,name,loginname,password from syslogins WHERE password IS NOT NULL
select  convert(varbinary(255), isnull(password, '')) passwd,sid,name,loginname,password from syslogins WHERE password IS NOT NULL

查看数据库映射用户 ​

sql
use hantest
-- 查看登录名和数据库的用户名的映射
SELECT DP.name as[user_name],SP.name as [logion_name] FROM sys.database_principals DP ,sys.server_principals SP WHERE SP.sid = DP.sid
use hantest
-- 查看登录名和数据库的用户名的映射
SELECT DP.name as[user_name],SP.name as [logion_name] FROM sys.database_principals DP ,sys.server_principals SP WHERE SP.sid = DP.sid

查看账号在每个db的数据库角色清单 ​

==保证每个库字符集相同，否则无法计算==

sql
USE master
GO 

IF OBJECT_ID('tempdb..#tmp') is not null
DROP TABLE #tmp

CREATE TABLE #tmp(id int primary key identity(1,1), username nvarchar(200), databasename nvarchar(200), privs nvarchar(200))
DECLARE @databasename nvarchar(1000)
DECLARE @sql nvarchar(max)

-- .过滤database_name
DECLARE database_cursor CURSOR FOR
SELECT name FROM sys.databases WHERE state = 0 -- AND name LIKE 'DBName%' 
ORDER BY name 

OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @databasename
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @sql = N'USE ' + QUOTENAME(@databasename) + ';
  select u.name, db_name(), r.name
  from sys.database_principals u
  inner join sys.database_role_members map on u.principal_id = map.member_principal_id
  inner join sys.database_principals r on map.role_principal_id = r.principal_id
  inner join sys.sql_logins l on u.name = l.name and is_disabled = 0
  where u.name <> ''dbo'''

  INSERT INTO #tmp (username, databasename, privs)
  EXEC (@sql)

  FETCH NEXT FROM database_cursor INTO @databasename
END
CLOSE database_cursor
DEALLOCATE database_cursor

INSERT INTO #tmp (username, databasename, privs)
SELECT u.name, NULL, p.name 
FROM sys.server_role_members r
INNER JOIN sys.server_principals p ON r.role_principal_id = p.principal_id AND p.type = 'R' 
INNER JOIN sys.sql_logins u ON r.member_principal_id = u.principal_id AND u.is_disabled = 0 
WHERE r.member_principal_id <> 1 

SELECT t2.username, t2.databasename, group_concat = STUFF(( SELECT ',' + privs FROM #tmp AS t1 WHERE t1.username = t2.username AND t1.databasename = t2.databasename FOR xml path('')) , 1 , 1 , '') 
FROM #tmp AS t2 
WHERE t2.username IN(SELECT name FROM sys.sql_logins) 
AND t2.databasename IS NOT NULL 
GROUP BY t2.username, t2.databasename
USE master
GO 

IF OBJECT_ID('tempdb..#tmp') is not null
DROP TABLE #tmp

CREATE TABLE #tmp(id int primary key identity(1,1), username nvarchar(200), databasename nvarchar(200), privs nvarchar(200))
DECLARE @databasename nvarchar(1000)
DECLARE @sql nvarchar(max)

-- .过滤database_name
DECLARE database_cursor CURSOR FOR
SELECT name FROM sys.databases WHERE state = 0 -- AND name LIKE 'DBName%' 
ORDER BY name 

OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @databasename
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @sql = N'USE ' + QUOTENAME(@databasename) + ';
  select u.name, db_name(), r.name
  from sys.database_principals u
  inner join sys.database_role_members map on u.principal_id = map.member_principal_id
  inner join sys.database_principals r on map.role_principal_id = r.principal_id
  inner join sys.sql_logins l on u.name = l.name and is_disabled = 0
  where u.name <> ''dbo'''

  INSERT INTO #tmp (username, databasename, privs)
  EXEC (@sql)

  FETCH NEXT FROM database_cursor INTO @databasename
END
CLOSE database_cursor
DEALLOCATE database_cursor

INSERT INTO #tmp (username, databasename, privs)
SELECT u.name, NULL, p.name 
FROM sys.server_role_members r
INNER JOIN sys.server_principals p ON r.role_principal_id = p.principal_id AND p.type = 'R' 
INNER JOIN sys.sql_logins u ON r.member_principal_id = u.principal_id AND u.is_disabled = 0 
WHERE r.member_principal_id <> 1 

SELECT t2.username, t2.databasename, group_concat = STUFF(( SELECT ',' + privs FROM #tmp AS t1 WHERE t1.username = t2.username AND t1.databasename = t2.databasename FOR xml path('')) , 1 , 1 , '') 
FROM #tmp AS t2 
WHERE t2.username IN(SELECT name FROM sys.sql_logins) 
AND t2.databasename IS NOT NULL 
GROUP BY t2.username, t2.databasename

1.5默认架构 ​

1.5.0创建默认架构 ​

https://docs.microsoft.com/zh-cn/sql/t-sql/statements/create-schema-transact-sql?view=sql-server-2017

  • 语法
sql
-- Syntax for SQL Server and Azure SQL Database  
  
CREATE SCHEMA schema_name_clause [ <schema_element> [ ...n ] ]  
  
<schema_name_clause> ::=  
    {  
    schema_name  
    | AUTHORIZATION owner_name  
    | schema_name AUTHORIZATION owner_name  
    }  
  
<schema_element> ::=   
    {   
        table_definition | view_definition | grant_statement |   
        revoke_statement | deny_statement   
    }
-- Syntax for SQL Server and Azure SQL Database  
  
CREATE SCHEMA schema_name_clause [ <schema_element> [ ...n ] ]  
  
<schema_name_clause> ::=  
    {  
    schema_name  
    | AUTHORIZATION owner_name  
    | schema_name AUTHORIZATION owner_name  
    }  
  
<schema_element> ::=   
    {   
        table_definition | view_definition | grant_statement |   
        revoke_statement | deny_statement   
    }
sql
use hantest
GO
CREATE SCHEMA customer_services AUTHORIZATION  hantestuser
GO
use hantest
GO
CREATE SCHEMA customer_services AUTHORIZATION  hantestuser
GO

1.5.1更改用户的默认架构 ​

sql
ALTER USER testuser1 WITH DEFAULT_SCHEMA = Purchasing;
GO
ALTER USER testuser1 WITH DEFAULT_SCHEMA = Purchasing;
GO
  • 同时更改
sql
ALTER USER Philip
WITH NAME = Philipe
, DEFAULT_SCHEMA = Development
, PASSWORD = 'W1r77TT98%ab@#' OLD_PASSWORD = 'New Devel0per'
, DEFAULT_LANGUAGE= French ;
GO
ALTER USER Philip
WITH NAME = Philipe
, DEFAULT_SCHEMA = Development
, PASSWORD = 'W1r77TT98%ab@#' OLD_PASSWORD = 'New Devel0per'
, DEFAULT_LANGUAGE= French ;
GO

1.5.3查询所有架构 ​

sql
SELECT s.name AS schema_name,u.name AS schema_owner FROM sys.schemas s INNER JOIN sys.sysusers u ON u.uid = s.principal_id ORDER BY s.name;
SELECT s.name AS schema_name,u.name AS schema_owner FROM sys.schemas s INNER JOIN sys.sysusers u ON u.uid = s.principal_id ORDER BY s.name;

1.5.4删除架构 ​

sql
DROP SCHEMA [IF EXISTS] schema_name;
DROP SCHEMA [IF EXISTS] schema_name;

1.6角色 ​

角色是一类权限的组合。

1.6.0数据库角色授权 ​

9中固定数据库角色

"db_owner" --拥有数据库全部权限，包括删除数据库权限

"db_accessadmin" --只给数据库用户创建其他数据库用户的权限，而没有创建登录用户的权限。

"db_securityadmin" --可以管理全部权限、对象所有权、角色和角色成员资格

"db_ddladmin" --可以发出所有DDL(Create,Alter和Drop)，但不能发出GRANT、REVOKE或DENY语句

"db_backupoperator" --允许对数据库进行备份和还原的权限【备份与还原是通过sql sever management studio也可以进行】

"db_datareader" --可以选择数据库内任何用户表中的所有数据

"db_datawriter" --可以更改数据库内任何用户表中的所有数据

"db_denydatareader" --不能查询数据库内任何用户表中的任何数据

"db_denydatawriter" --不能更改数据库内任何用户表中的任何数据

==DataBase级别的角色作用范围是整个DB的，比如db_datareader，db_datawriter都是作用在数据库所有的对象==

  • 语法

https://docs.microsoft.com/zh-cn/sql/relational-databases/system-stored-procedures/sp-addrolemember-transact-sql?view=sql-server-2017

sql
sp_addrolemember [ @rolename = ] 'role', [ @membername = ] 'security_account'


[ @rolename= ] "@rolename="
当前数据库中的数据库角色的名称。 role 是 sysname，无默认值。

[ @membername= ] '@membername='
添加到该角色中的安全帐户。 security_account 是 sysname，无默认值。 security_account可以是数据库用户、数据库角色、Windows 登录名或 Windows 组
sp_addrolemember [ @rolename = ] 'role', [ @membername = ] 'security_account'


[ @rolename= ] "@rolename="
当前数据库中的数据库角色的名称。 role 是 sysname，无默认值。

[ @membername= ] '@membername='
添加到该角色中的安全帐户。 security_account 是 sysname，无默认值。 security_account可以是数据库用户、数据库角色、Windows 登录名或 Windows 组
sql
EXEC sp_addrolemember "db_datareader", "hantestuser"
EXEC sp_addrolemember "db_datareader", "hantestuser"

1.6.0 创建角色 ​

sql
USE db_name

--创建角色 r_test
EXEC sp_addrole 'r_test'
USE db_name

--创建角色 r_test
EXEC sp_addrole 'r_test'

1.6.1查看用户角色授权 ​

sql
SELECT p.name as UserName, p.type_desc as UserType, pp.name as DBRoleName, pp.type_desc as DBRoleType, pp.is_fixed_role as IfFixedRole

FROM sys.database_role_members roles

JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id

JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id

where pp.name in('db_owner', 'db_datareader')
SELECT p.name as UserName, p.type_desc as UserType, pp.name as DBRoleName, pp.type_desc as DBRoleType, pp.is_fixed_role as IfFixedRole

FROM sys.database_role_members roles

JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id

JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id

where pp.name in('db_owner', 'db_datareader')

1.6.2查看服务器角色授权 ​

sql
SELECT dp.name as UserName, dp.type_desc as UserType, sp.name as LoginName, sp.type_desc as LoginType FROM sys.database_principals dp JOIN sys.server_principals sp ON dp.principal_id = sp.principal_id order by UserType
SELECT dp.name as UserName, dp.type_desc as UserType, sp.name as LoginName, sp.type_desc as LoginType FROM sys.database_principals dp JOIN sys.server_principals sp ON dp.principal_id = sp.principal_id order by UserType

1.6.3删除数据库用户角色 ​

sql
use hantest
exec sp_droprolemember 'db_datareader','hantestuser'
use hantest
exec sp_droprolemember 'db_datareader','hantestuser'

1.6.4查询一个UserName拥有的所有权限 ​

通过角色集成的权限和自身具备的权限

sql
--
-- 查询一个UserName拥有的角色以及角色拥有的操作对象, xxx 根据自己的名字
DECLARE @login_name varchar(100) = 'xxx'

;WITH LoginName
AS
(
    SELECT  u.name            AS LoginName,
            r.name          AS RoleName,
        role_principal_id   AS PrincipalId
    FROM sys.database_role_members AS m
    INNER JOIN sys.database_principals AS r ON r.principal_id = m.role_principal_id
    INNER JOIN sys.database_principals AS u ON u.principal_id = m.member_principal_id
),
UserPermission
AS
(
    select USER_NAME(p.grantee_principal_id)   AS principal_name,
            dp.principal_id                    AS principal_id,
            dp.type_desc                       AS principal_type_desc,
            p.class_desc                       AS class_desc,
            OBJECT_NAME(p.major_id)            AS object_name,
            p.permission_name                  AS permission_name,
            p.state_desc                       AS permission_state_desc
    from sys.database_permissions p
    INNER JOIN sys.database_principals dp on  p.grantee_principal_id = dp.principal_id
)
SELECT * FROM
(

    -- 通过角色获取的权限对象
    SELECT  u.LoginName,
            u.RoleName,
            p.principal_type_desc,
            p.class_desc,
            p.permission_name,
            p.object_name,
            p.permission_state_desc
    FROM LoginName u left join UserPermission p on p.principal_name = u.RoleName
    WHERE u.LoginName = @login_name
      
    UNION ALL
    -- 直接授权给账号的权限对象
    select      @login_name             AS LoginName,
                ''                      AS RoleName,
                dp.type_desc            AS principal_type_desc,
                p.class_desc            AS class_desc,
                p.permission_name       AS permission_name,
                OBJECT_NAME(p.major_id) AS object_name,
                p.state_desc            AS permission_state_desc
    from sys.database_permissions p
                INNER JOIN sys.database_principals dp on  p.grantee_principal_id = dp.principal_id
    where USER_NAME(p.grantee_principal_id) = @login_name

    UNION ALL
    -- 固定服务器角色的权限
    SELECT      r.name            ,
                cast(r.principal_id as varchar(10))    ,
                r.type_desc,
                null as class_desc,
                null as object_name,
                p2.name as permission_name,
                null as permission_state_desc
    FROM sys.server_principals r
    INNER JOIN sys.server_role_members m ON r.principal_id = m.member_principal_id
    INNER JOIN sys.server_principals p1 ON p1.principal_id = m.member_principal_id
    INNER JOIN sys.server_principals p2 ON p2.principal_id = m.role_principal_id  
    WHERE r.name = @login_name
)t
--
-- 查询一个UserName拥有的角色以及角色拥有的操作对象, xxx 根据自己的名字
DECLARE @login_name varchar(100) = 'xxx'

;WITH LoginName
AS
(
    SELECT  u.name            AS LoginName,
            r.name          AS RoleName,
        role_principal_id   AS PrincipalId
    FROM sys.database_role_members AS m
    INNER JOIN sys.database_principals AS r ON r.principal_id = m.role_principal_id
    INNER JOIN sys.database_principals AS u ON u.principal_id = m.member_principal_id
),
UserPermission
AS
(
    select USER_NAME(p.grantee_principal_id)   AS principal_name,
            dp.principal_id                    AS principal_id,
            dp.type_desc                       AS principal_type_desc,
            p.class_desc                       AS class_desc,
            OBJECT_NAME(p.major_id)            AS object_name,
            p.permission_name                  AS permission_name,
            p.state_desc                       AS permission_state_desc
    from sys.database_permissions p
    INNER JOIN sys.database_principals dp on  p.grantee_principal_id = dp.principal_id
)
SELECT * FROM
(

    -- 通过角色获取的权限对象
    SELECT  u.LoginName,
            u.RoleName,
            p.principal_type_desc,
            p.class_desc,
            p.permission_name,
            p.object_name,
            p.permission_state_desc
    FROM LoginName u left join UserPermission p on p.principal_name = u.RoleName
    WHERE u.LoginName = @login_name
      
    UNION ALL
    -- 直接授权给账号的权限对象
    select      @login_name             AS LoginName,
                ''                      AS RoleName,
                dp.type_desc            AS principal_type_desc,
                p.class_desc            AS class_desc,
                p.permission_name       AS permission_name,
                OBJECT_NAME(p.major_id) AS object_name,
                p.state_desc            AS permission_state_desc
    from sys.database_permissions p
                INNER JOIN sys.database_principals dp on  p.grantee_principal_id = dp.principal_id
    where USER_NAME(p.grantee_principal_id) = @login_name

    UNION ALL
    -- 固定服务器角色的权限
    SELECT      r.name            ,
                cast(r.principal_id as varchar(10))    ,
                r.type_desc,
                null as class_desc,
                null as object_name,
                p2.name as permission_name,
                null as permission_state_desc
    FROM sys.server_principals r
    INNER JOIN sys.server_role_members m ON r.principal_id = m.member_principal_id
    INNER JOIN sys.server_principals p1 ON p1.principal_id = m.member_principal_id
    INNER JOIN sys.server_principals p2 ON p2.principal_id = m.role_principal_id  
    WHERE r.name = @login_name
)t

1.6.5查询某个账号有哪些权限 ​

sql
--查询某个账号有哪些权限，直接授权给账号的,而不是通过角色继承来的,xxx根据自己填写
select USER_NAME(p.grantee_principal_id) AS principal_name,
        p.grantee_principal_id,
        dp.principal_id,
        dp.type_desc AS principal_type_desc,
        p.class_desc,
        OBJECT_NAME(p.major_id) AS object_name,
        p.permission_name,
        p.state_desc AS permission_state_desc
from sys.database_permissions p 
        INNER JOIN sys.database_principals dp on  p.grantee_principal_id = dp.principal_id 
where USER_NAME(p.grantee_principal_id) = 'xxx'
--查询某个账号有哪些权限，直接授权给账号的,而不是通过角色继承来的,xxx根据自己填写
select USER_NAME(p.grantee_principal_id) AS principal_name,
        p.grantee_principal_id,
        dp.principal_id,
        dp.type_desc AS principal_type_desc,
        p.class_desc,
        OBJECT_NAME(p.major_id) AS object_name,
        p.permission_name,
        p.state_desc AS permission_state_desc
from sys.database_permissions p 
        INNER JOIN sys.database_principals dp on  p.grantee_principal_id = dp.principal_id 
where USER_NAME(p.grantee_principal_id) = 'xxx'

1.6.6查询某个User有哪些角色的权限 ​

sql


--某个User有哪些角色的权限（User属于哪一个（多个）角色）
SELECT u.name, r.name
FROM sys.database_role_members AS m
        INNER JOIN sys.database_principals AS r ON m.role_principal_id = r.principal_id
        INNER JOIN sys.database_principals AS u ON u.principal_id = m.member_principal_id
WHERE u.name = 'ReadUser'; --UserName


--某个User有哪些角色的权限（User属于哪一个（多个）角色）
SELECT u.name, r.name
FROM sys.database_role_members AS m
        INNER JOIN sys.database_principals AS r ON m.role_principal_id = r.principal_id
        INNER JOIN sys.database_principals AS u ON u.principal_id = m.member_principal_id
WHERE u.name = 'ReadUser'; --UserName

1.6.7查询某个自定义角色拥有哪些权限 ​

sql
--查询某个角色拥有的权限
select USER_NAME(p.grantee_principal_id) AS principal_name,
            dp.principal_id,
            dp.type_desc AS principal_type_desc,
            p.class_desc,
            OBJECT_NAME(p.major_id) AS object_name,
            p.permission_name,
            p.state_desc AS permission_state_desc
from sys.database_permissions p 
            INNER JOIN sys.database_principals dp on  p.grantee_principal_id = dp.principal_id 
where USER_NAME(p.grantee_principal_id) =  'ReadRole' -- 角色名称
--查询某个角色拥有的权限
select USER_NAME(p.grantee_principal_id) AS principal_name,
            dp.principal_id,
            dp.type_desc AS principal_type_desc,
            p.class_desc,
            OBJECT_NAME(p.major_id) AS object_name,
            p.permission_name,
            p.state_desc AS permission_state_desc
from sys.database_permissions p 
            INNER JOIN sys.database_principals dp on  p.grantee_principal_id = dp.principal_id 
where USER_NAME(p.grantee_principal_id) =  'ReadRole' -- 角色名称

1.6.8 查看用户被赋予的权限 ​

sql
-- use PointsMall_Manage
use DB_name
EXEC sp_helprotect NULL, 'userName'

# 或者
use DB_name
exec sp_helprotect @username = 'haojianyu'
-- use PointsMall_Manage
use DB_name
EXEC sp_helprotect NULL, 'userName'

# 或者
use DB_name
exec sp_helprotect @username = 'haojianyu'

1.6.9列出作为数据库级别角色成员 ​

sql
use DB_name
SELECT    roles.principal_id                            AS RolePrincipalID
    ,    roles.name                                    AS RolePrincipalName
    ,    database_role_members.member_principal_id    AS MemberPrincipalID
    ,    members.name                                AS MemberPrincipalName
FROM sys.database_role_members AS database_role_members  
JOIN sys.database_principals AS roles  
    ON database_role_members.role_principal_id = roles.principal_id  
JOIN sys.database_principals AS members  
    ON database_role_members.member_principal_id = members.principal_id;
use DB_name
SELECT    roles.principal_id                            AS RolePrincipalID
    ,    roles.name                                    AS RolePrincipalName
    ,    database_role_members.member_principal_id    AS MemberPrincipalID
    ,    members.name                                AS MemberPrincipalName
FROM sys.database_role_members AS database_role_members  
JOIN sys.database_principals AS roles  
    ON database_role_members.role_principal_id = roles.principal_id  
JOIN sys.database_principals AS members  
    ON database_role_members.member_principal_id = members.principal_id;

1.6.9 用户所属数据库角色 ​

sql
use DB_name
select DbRole = g.name, MemberName = u.name, MemberSID = u.sid
  from sys.database_principals u, sys.database_principals g, sys.database_role_members m
 where g.principal_id = m.role_principal_id
   and u.principal_id = m.member_principal_id
 order by 1, 2
use DB_name
select DbRole = g.name, MemberName = u.name, MemberSID = u.sid
  from sys.database_principals u, sys.database_principals g, sys.database_role_members m
 where g.principal_id = m.role_principal_id
   and u.principal_id = m.member_principal_id
 order by 1, 2

案例 ​

  • 角色
sql
USE master
GO
--创建一个用户
CREATE LOGIN ReadUser WITH PASSWORD ='123qwe!@#',DEFAULT_DATABASE=DBTest


USE DBTest
GO
 --创建数据库用户，指定到上面
CREATE USER ReadUser FOR LOGIN ReadUser WITH DEFAULT_SCHEMA = dbo


USE DBTest
GO
create role ReadRole

-- 将多张表的权限授予新建的ReadRole
grant select on dbo.DEPT to ReadRole

-- 将Readuser 加入到ReadRole这个角色中
alter role ReadRole add member ReadUser
USE master
GO
--创建一个用户
CREATE LOGIN ReadUser WITH PASSWORD ='123qwe!@#',DEFAULT_DATABASE=DBTest


USE DBTest
GO
 --创建数据库用户，指定到上面
CREATE USER ReadUser FOR LOGIN ReadUser WITH DEFAULT_SCHEMA = dbo


USE DBTest
GO
create role ReadRole

-- 将多张表的权限授予新建的ReadRole
grant select on dbo.DEPT to ReadRole

-- 将Readuser 加入到ReadRole这个角色中
alter role ReadRole add member ReadUser

1.7服务器角色 ​

官方文档，https://docs.microsoft.com/zh-cn/sql/relational-databases/security/authentication-access/server-level-roles?view=sql-server-2017

服务器角色的拥有者只有登入名，服务器角色是固定的，用户无法创建服务器角色.

注意：一般不建议给用户直接分配服务器角色，因为服务器角色是全局的，也就是说你拥有了服务器级别的权限，一般建议给用户分配数据库，然后给对应的数据库分配数据库角色权限

1.7用户grant授权 ​

官方文档，https://docs.microsoft.com/zh-cn/sql/t-sql/statements/grant-schema-permissions-transact-sql?view=sql-server-2017

  • 语法
sql
GRANT <permission> [ ,...n ]
    TO <database_principal> [ ,...n ] [ WITH GRANT OPTION ]
    [ AS <database_principal> ]

<permission>::=
permission | ALL [ PRIVILEGES ]

<database_principal> ::=
    Database_user
  | Database_role
  | Application_role
  | Database_user_mapped_to_Windows_User
  | Database_user_mapped_to_Windows_Group
  | Database_user_mapped_to_certificate
  | Database_user_mapped_to_asymmetric_key
  | Database_user_with_no_login
GRANT <permission> [ ,...n ]
    TO <database_principal> [ ,...n ] [ WITH GRANT OPTION ]
    [ AS <database_principal> ]

<permission>::=
permission | ALL [ PRIVILEGES ]

<database_principal> ::=
    Database_user
  | Database_role
  | Application_role
  | Database_user_mapped_to_Windows_User
  | Database_user_mapped_to_Windows_Group
  | Database_user_mapped_to_certificate
  | Database_user_mapped_to_asymmetric_key
  | Database_user_with_no_login

https://www.cnblogs.com/gered/p/9378937.html#_label0_1

1.7.1 revoke ​

USE InsideTSQL2008

--创建角色 r_test
EXEC sp_addrole 'r_test'

--添加登录 l_test,设置密码为pwd,默认数据库为pubs
EXEC sp_addlogin 'l_test','a@cd123','InsideTSQL2008'

--为登录 l_test 在数据库 pubs 中添加安全账户 u_test
EXEC sp_grantdbaccess 'l_test','u_test'

--添加 u_test 为角色 r_test 的成员
EXEC sp_addrolemember 'r_test','u_test'


--用l_test登陆,发现在SSMS中找不到仍和表，因此执行下述两条语句出错。
select * from Sales.Orders
select * from HR.Employees

--授予角色 r_test 对 HR.Employees 表的所有权限
GRANT ALL ON HR.Employees TO r_test
--The ALL permission is deprecated and maintained only for compatibility. 
--It DOES NOT imply ALL permissions defined on the entity.
--ALL 权限已不再推荐使用，并且只保留用于兼容性目的。它并不表示对实体定义了 ALL 权限。

--测试可以查询表HR.Employees，但是Sales.Orders无法查询
select * from HR.Employees


--如果要收回权限，可以使用如下语句。（可选择执行）
revoke all on HR.Employees from r_test
--ALL 权限已不再推荐使用，并且只保留用于兼容性目的。它并不表示对实体定义了 ALL 权限。


--授予角色 r_test 对 Sales.Orders 表的 SELECT 权限
GRANT SELECT ON Sales.Orders TO r_test

--用l_test登陆,发现可以查询Sales.Orders和HR.Employees两张表
select * from Sales.Orders
select * from HR.Employees

--拒绝安全账户 u_test 对 HR.Employees 表的 SELECT 权限
DENY SELECT ON HR.Employees TO u_test

--再次执行查询HR.Employees表的语句，提示：拒绝了对对象 'Employees' (数据库 'InsideTSQL2008'，架构 'HR')的 SELECT 权限。
select * from HR.Employees

--重新授权
GRANT SELECT ON HR.Employees TO u_test

--再次查询，可以查询出结果。
select * from HR.Employees


USE InsideTSQL2008
--从数据库中删除安全账户,failed
EXEC sp_revokedbaccess 'u_test'
--删除角色 r_test,failed
EXEC sp_droprole 'r_test'
--删除登录 l_test,success
EXEC sp_droplogin 'l_test'
USE InsideTSQL2008

--创建角色 r_test
EXEC sp_addrole 'r_test'

--添加登录 l_test,设置密码为pwd,默认数据库为pubs
EXEC sp_addlogin 'l_test','a@cd123','InsideTSQL2008'

--为登录 l_test 在数据库 pubs 中添加安全账户 u_test
EXEC sp_grantdbaccess 'l_test','u_test'

--添加 u_test 为角色 r_test 的成员
EXEC sp_addrolemember 'r_test','u_test'


--用l_test登陆,发现在SSMS中找不到仍和表，因此执行下述两条语句出错。
select * from Sales.Orders
select * from HR.Employees

--授予角色 r_test 对 HR.Employees 表的所有权限
GRANT ALL ON HR.Employees TO r_test
--The ALL permission is deprecated and maintained only for compatibility. 
--It DOES NOT imply ALL permissions defined on the entity.
--ALL 权限已不再推荐使用，并且只保留用于兼容性目的。它并不表示对实体定义了 ALL 权限。

--测试可以查询表HR.Employees，但是Sales.Orders无法查询
select * from HR.Employees


--如果要收回权限，可以使用如下语句。（可选择执行）
revoke all on HR.Employees from r_test
--ALL 权限已不再推荐使用，并且只保留用于兼容性目的。它并不表示对实体定义了 ALL 权限。


--授予角色 r_test 对 Sales.Orders 表的 SELECT 权限
GRANT SELECT ON Sales.Orders TO r_test

--用l_test登陆,发现可以查询Sales.Orders和HR.Employees两张表
select * from Sales.Orders
select * from HR.Employees

--拒绝安全账户 u_test 对 HR.Employees 表的 SELECT 权限
DENY SELECT ON HR.Employees TO u_test

--再次执行查询HR.Employees表的语句，提示：拒绝了对对象 'Employees' (数据库 'InsideTSQL2008'，架构 'HR')的 SELECT 权限。
select * from HR.Employees

--重新授权
GRANT SELECT ON HR.Employees TO u_test

--再次查询，可以查询出结果。
select * from HR.Employees


USE InsideTSQL2008
--从数据库中删除安全账户,failed
EXEC sp_revokedbaccess 'u_test'
--删除角色 r_test,failed
EXEC sp_droprole 'r_test'
--删除登录 l_test,success
EXEC sp_droplogin 'l_test'

1.7.2 deny ​

1.8存储创建角色 ​

sp_addlogin 登录名，登陆密码，默认数据库，默认语言，安全码，是否加密

sp_password 旧密码，新密码，指定登录号

sp_defaultdb 指定登录号，默认数据库

sp_defaultlanguage 指定登录号，默认语言

sp_helplogins 指定登录号

sp_droplogin 指定登录号

-------------------------数据库用户管理--- sp_grantdbaccess 登录号，数据库用户名

sp_helpuser 数据库用户名

sp_revokedbaccess 指定数据库用户名

------------------------服务器角色-------- sp_addsrvrolermemeber 登陆账号名，服务器角色名

sp_dropsrvrolermember 登陆用户名，服务器角色名

------------------------数据库角色---------

sp_addrole 数据库角色名，数据库角色的所有者

sp_droprole 数据库角色名

------------创建数据库角色的成员----

sp_addrolemember 数据库角色名，数据库用户

sp_droprolemember 数据库角色名，数据库用户

1.8.1创建角色 ​

sql
--创建一个类似于db_datareader的角色,数据库会存在数据库架构sys.schemas中
EXEC sp_addrole "[db_selectUpdate]" 

--给该角色赋予查询和修改的权限
GRANT SELECT, UPDATE TO [db_selectUpdate] 

--给新建的数据库用户分配刚才赋予的权限【之后想只给查看和修改权限的就用新建的就可以了】
EXEC sp_addrolemember "[db_selectUpdate]", "[DatabaseUserName]"
--创建一个类似于db_datareader的角色,数据库会存在数据库架构sys.schemas中
EXEC sp_addrole "[db_selectUpdate]" 

--给该角色赋予查询和修改的权限
GRANT SELECT, UPDATE TO [db_selectUpdate] 

--给新建的数据库用户分配刚才赋予的权限【之后想只给查看和修改权限的就用新建的就可以了】
EXEC sp_addrolemember "[db_selectUpdate]", "[DatabaseUserName]"

1.8.2删除角色 ​

sql
--删除角色 r_test,failed
use data_name
EXEC sp_droprole 'role_name'

--删除登录 l_test,success
EXEC sp_droplogin 'login_name'
--删除角色 r_test,failed
use data_name
EXEC sp_droprole 'role_name'

--删除登录 l_test,success
EXEC sp_droplogin 'login_name'

完整案例 ​

sql
-- create user 完整案例
use master
CREATE login dba WITH password = '340$Uuxwp7Mcxo7Khy', default_database= PointsMall_Manage


USE PointsMall_Organization
CREATE USER [dba] FOR login [haojianyu] WITH default_schema = dbo
-- 上面两步只能登陆和看到数据库，表并不能看到



use PointsMall_Manage
exec sp_addrolemember 'db_owner', 'dba'
-- 



#如果是多个数据库
-- 让 SQL Server 登陆帐户“dba”访问多个数据库
use mydb2
go
create user dba for login dba with default_schema=dbo
go
exec sp_addrolemember 'db_owner', 'dba'
go



-- 查询用户权限
select USER_NAME(p.grantee_principal_id) AS principal_name,
        p.grantee_principal_id,
        dp.principal_id,
        dp.type_desc AS principal_type_desc,
        p.class_desc,
        OBJECT_NAME(p.major_id) AS object_name,
        p.permission_name,
        p.state_desc AS permission_state_desc
from sys.database_permissions p 
        INNER JOIN sys.database_principals dp on  p.grantee_principal_id = dp.principal_id 
where USER_NAME(p.grantee_principal_id) = 'haojianyu'
-- create user 完整案例
use master
CREATE login dba WITH password = '340$Uuxwp7Mcxo7Khy', default_database= PointsMall_Manage


USE PointsMall_Organization
CREATE USER [dba] FOR login [haojianyu] WITH default_schema = dbo
-- 上面两步只能登陆和看到数据库，表并不能看到



use PointsMall_Manage
exec sp_addrolemember 'db_owner', 'dba'
-- 



#如果是多个数据库
-- 让 SQL Server 登陆帐户“dba”访问多个数据库
use mydb2
go
create user dba for login dba with default_schema=dbo
go
exec sp_addrolemember 'db_owner', 'dba'
go



-- 查询用户权限
select USER_NAME(p.grantee_principal_id) AS principal_name,
        p.grantee_principal_id,
        dp.principal_id,
        dp.type_desc AS principal_type_desc,
        p.class_desc,
        OBJECT_NAME(p.major_id) AS object_name,
        p.permission_name,
        p.state_desc AS permission_state_desc
from sys.database_permissions p 
        INNER JOIN sys.database_principals dp on  p.grantee_principal_id = dp.principal_id 
where USER_NAME(p.grantee_principal_id) = 'haojianyu'

权限传递 ​

用sa（SQL Server管理猿帐号）创建UserA, UserB。然后把SELECT News表的授权给UserA，再让UserA授权给UserB，最后回收UserA的权限，看看UserB还有木有权限

首先，创建用户的脚本如下：

-- Create a SQL Server login named UserA, and then creates a corresponding database user UserA in TestDb.
CREATE LOGIN UserA WITH PASSWORD = 'aaa@123';
USE TestDb;
GO
CREATE USER UserA FOR LOGIN UserA;
GO

-- do the same for UserB
CREATE LOGIN UserB WITH PASSWORD = 'bbb@123';
USE TestDb;
GO
CREATE USER UserB FOR LOGIN UserB;
GO
-- Create a SQL Server login named UserA, and then creates a corresponding database user UserA in TestDb.
CREATE LOGIN UserA WITH PASSWORD = 'aaa@123';
USE TestDb;
GO
CREATE USER UserA FOR LOGIN UserA;
GO

-- do the same for UserB
CREATE LOGIN UserB WITH PASSWORD = 'bbb@123';
USE TestDb;
GO
CREATE USER UserB FOR LOGIN UserB;
GO

然后，我要给UserA授权，不然UserA登录数据库的时候，是看不见任何表的，也不能SELECT

GRANT SELECT ON TestDb.dbo.News TO UserA



GRANT SELECT ON TestDb.dbo.News TO UserA WITH GRANT OPTION
GRANT SELECT ON TestDb.dbo.News TO UserA



GRANT SELECT ON TestDb.dbo.News TO UserA WITH GRANT OPTION

在回收传递的权限时，必须在REVOKE语句的最后加上CASCADE：

vbnet
-- do revoke
REVOKE SELECT ON TestDb.dbo.News TO UserA CASCADE
-- do revoke
REVOKE SELECT ON TestDb.dbo.News TO UserA CASCADE

https://blog.csdn.net/xiaochao2011111167/article/details/87272038

https://www.yiibai.com/sqlserver/what-is-sql-server.html

https://blog.csdn.net/weixin_45816550/article/details/109347333

https://blog.csdn.net/qq_38950819/article/details/121269948

https://blog.csdn.net/zhoujunah/article/details/104396000

https://blog.csdn.net/qq_43974000/article/details/105309576

https://www.cnblogs.com/programsky/archive/2018/07/05/9268823.html

https://www.cnblogs.com/zhy-1992/p/6743511.html

https://docs.microsoft.com/zh-cn/sql/t-sql/statements/alter-role-transact-sql?view=sql-server-ver15

https://docs.microsoft.com/zh-cn/sql/t-sql/statements/grant-schema-permissions-transact-sql?view=sql-server-2017

https://www.cnblogs.com/jennyjiang-00/p/5803047.html

https://www.cnblogs.com/txdblog/p/15222291.html

https://developer.aliyun.com/article/15905

https://www.cnblogs.com/bandaobudaoweng/p/9958126.html

https://www.cnblogs.com/willingtolove/p/9122738.html

https://blog.51cto.com/abnerluo/1320530

https://www.jc2182.com/sqlserver/sql-server-architecture.html

https://www.cnblogs.com/wy123/category/834336.html

https://www.cnblogs.com/vuenote/category/1266651.html

https://docs.microsoft.com/zh-cn/sql/relational-databases/security/authentication-access/getting-started-with-database-engine-permissions?view=sql-server-2017

https://www.yht7.com/news/1867

2.图形界面创建 ​