SQLServer中创建用户角色及授权

在SQL Server中创建用户角色及授权

假设，我们准备建立一个 dba 数据库帐户，用来管理数据库 mydb。

1. 首先在 SQL Server 服务器级别，创建登陆帐户（create login）

--创建登陆帐户（create login）
create login dba with password='abcd1234@', default_database=mydb
登陆帐户名为：“dba”，登陆密码：“abcd1234@”，默认连接到的数据库：“mydb”。
这时候，dba 帐户就可以连接到 SQL Server 服务器上了。但是此时还不能 访问数据库中的对象（严格的说，此时 dba 帐户默认是 guest 数据库用户身份， 可以访问 guest 能够访问的数据库对象）。
要使 dba 帐户能够在mydb数据库中访问自己需要的对象，需要在数据库 mydb 中建立一个“数据库用户”，赋予这个“数据库用户” 某些访问权限，并且把登陆帐户“dba” 和这个“数据库用户”映射起来。习惯上，“数据库用户”的名字和“登陆帐户”的名字相同，即：“dba”。创建“数据库用户”和建立映射关系只需要一步即可完成：

2. 创建数据库用户（create user）：

--为登陆账户创建数据库用户（create user）,在mydb数据库中的security中的user下可以找到新创建的dba
create user dba for login dba with default_schema=dbo
并指定数据库用户“dba” 的默认 schema 是“dbo”。这意味着 用户“dba” 在执行“select * from t”，实际上执行的是 “select * from dbo.t”。

3. 通过加入数据库角色，赋予数据库用户“dba”权限：

--加入数据库角色，赋予“db_owner”权限 exec sp_addrolemember 'db_owner', 'dba'时，dba 就可以全权管理数据库 mydb 中的对象了。

如想让 SQL Server 登陆帐户“dba”访问多个数据库，比如 mydb2。 可以让 sa 执行下面的语句：

--让 SQL Server 登陆帐户“dba”访问多个数据库

use mydb2 go
create user dba for login dba with default_schema=dbo go
exec sp_addrolemember 'db_owner', 'dba' go
此时，dba 就可以有两个数据库 mydb, mydb2 的管理权限了！

示例在数据库InsideTSQL2008中创建一个拥有表HR.Employees的所有权限、拥有表Sales.Orders的SELECT权限的角色r_test。
随后创建了一个登录l_test，然后在数据库InsideTSQL2008中为登录l_test创建了用户账户u_test。
同时将用户账户u_test添加到角色r_test中，使其通过权限继承获取了与角色r_test一样的权限。
最后使用DENY语句拒绝了用户账户u_test对表HR.Employees的SELECT权限。
经过这样的处理，使用l_test登录SQL Server实例后，它只具有表Sales.Orders的select权限和对表HR.Employees出select外的所有权限。

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'

revoke 与 deny的区别

revoke：收回之前被授予的权限
deny：拒绝给当前数据库内的安全帐户授予权限并防止安全帐户通过其组或角色成员资格继承权限。
比如UserA所在的角色组有inset权限，但是我们Deny UserA使其没有insert权限，那么以后即使UserA再怎么到其他含有Insert的角色组中去，还是没有insert权限，除非该用户被显示授权。
简单来说，deny就是将来都不许给，revoke就是收回已经给予的。

实例

GRANT INSERT ON TableA TO RoleA      GO
EXEC sp_addrolemember RoleA, 'UserA'   GO 
-- 用户UserA将有TableA的INSERT权限
REVOKE INSERT ON TableA FROM RoleA   GO 
-- 用户UserA将没有TableA的INSERT权限，收回权限。  
GRANT INSERT ON TableA TORoleA  GO 
--重新给RoleA以TableA的INSERT权限
DENY INSERT ON TableA TO UserA 
-- 虽然用户UserA所在RoleA有TableA的INSERT权限，但UserA本身被DENY了，所以用户UserA将没有TableA的INSERT权限。

批量删除ms server数据表

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: <2022.03.23>
-- Description:	<批量删除MsServer数据库中以F_开头的用户数据表>
-- =============================================
alter PROCEDURE BatchDeleteTable
AS
BEGIN
DECLARE @cul int 
declare @tablename varchar(50)
declare @dropSql nvarchar(100)
select @cul=COUNT(name) from sysobjects where (left(name,2) = 'F_')  and type='U'
print @cul
 
while @cul>0
  begin
    select @tablename=tb1.name from (select name from sysobjects where (left(name,2) = 'F_')  and (type='U')) as tb1
    set @dropSql='drop table'+' '+@tablename;
    EXECUTE sp_executesql @dropSql 
    select @cul=COUNT(name) from sysobjects where (left(name,2) = 'F_') and type='U'
end
 
END
GO