思享工具箱

第二章 SQLserver常见用户授权配置

摘要：
1、 创建登录用户1。登录数据库#1。输入主数据库USE[master]#2。创建用户语法CREATELOGIN[登录名]WITH password=｛'password'| hashed_passwordashed｝[must_change][，--不能使用login_name@DomainNameUPN格式--密码='密码*'--仅适用于SQLServer登录“

一、创建登录用户

1.登录数据库

#1.进入master库
USE [master]

#2.创建用户语法
CREATE LOGIN [登录名] WITH
password={ 'password' | hashed_password hashed }
[must_change][,]
[sid=0x14585E90117152449347750164BA00A7][,]
[default_database=database_name][,]
[default_language=language][,]
[check_expiration={ on | off }][,]
[check_policy={ on | off }][,]
[credential=credential_name]

2.语法块含义

#1.--login_name	指定创建的登录名。

有四种类型的登录：SQLServer登录、Windows登录、证书映射登录和非对称密钥映射登录。
--在创建从Windows域帐户映射的登录名时，必须以[<domainName><login_name>]格式使用Windows 2000之前的用户登录名。 
--不能使用login_name@DomainName格式的UPN。 
--身份验证登录的类型为sysname，它必须符合标识符规则，且不能包含“”。 
--Windows登录名可以包含“”。Active Directory用户的登录名需少于21个字符。

#2.--password 指定创建用户的密码
有两种密码类型：
--password='password*' 
--仅适用于SQL Server登录。指定正在创建的登录名的密码。应使用强密码。 
--有关详细信息，请参阅强密码和密码策略。从SQL Server 2012 (11.x)开始，存储的密码信息使用 SHA-512 加盐密码进行计算。
--密码是区分大小写的。密码应始终至少包含 8 个字符，并且不能超过128个字符。 
--密码可以包含 a-z、A-Z、0-9 和大多数非字母数字字符。 密码不能包含单引号或 login_name。

--password=hashed_password
--仅适用于hashed关键字。指定要创建的登录名的密码的哈希值。
--hashed仅适用于SQL Server登录。指定在password参数后输入的密码已经过哈希运算。 
--如果未选择此选项，则在将作为密码输入的字符串存储到数据库中之前，对其进行哈希运算。 
--此选项应仅用于在服务器之间迁移数据库。切勿使用hashed选项创建新的登录名。hashed选项不能用于SQL 7或更早版本创建的哈希。

#3.--must_change 首次登录设置新密码
--仅适用于SQL Server登录。如果包括此选项，则SQL Server将在首次使用新登录时提示用户输入新密码。

#4.--sid=sid 用于重新创建登录名
--用于重新创建登录名。仅适用于SQL Server身份验证登录，不适用于Windows身份验证登录。指定新SQL Server身份验证登录的sid。
--如果未使用此选项，SQL Server将自动分配sid。sid结构取决于SQL Server版本。 QL Server登录sid：基于GUID的16 字节(binary(16))文本值。 例如，sid 0x14585E90117152449347750164BA00A7。

#5.--default_database=database 用于指定默认数据库
--指定将指派给登录名的默认数据库。如果未包括此选项，则默认数据库将设置为master。

#6.--default_language=language 用于指定登录的语言
--指定将指派给登录名的默认语言。如果未包括此选项，则默认语言将设置为服务器的当前默认语言。即使将来服务器的默认语言发生更改，登录名的默认语言也仍保持不变。

#7.--check_expiration={ on | off } 用于指定是否强制实施密码过期策略
--仅适用于SQL Server登录。 指定是否应对此登录帐户强制实施密码过期策略。 默认值为off。

#8.--check_policy={ on | off } 用于指定实施的密码策略
--仅适用于SQL Server登录。 指定应对此登录强制实施运行SQL Server 计算机的 Windows 密码策略。 默认值为on。
--如果 Windows 策略要求强密码，密码必须至少包含以下四个特点中的三个：
--大写字符 (A-Z)。
--小写字符 (a-z)。
--数字 (0-9)。
--一个非字母数字字符，如空格、、@、*、^、%、!、$、# 或 &。

#9.--credential=credential_name 用于映射登录凭据
--将映射到新SQL Server登录的凭据名称。 该凭据必须已存在于服务器中。当前此选项只将凭据链接到登录名。凭据不能映射到系统管理员(sa)登录名。

3.常用示例

#1.最快速创建方式：
USE [master]
CREATE LOGIN [登录名] WITH PASSWORD=N'密码', DEFAULT_DATABASE=[test], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

#2.普通创建方式：
--声明引用数据库
use tests;

--创建登录用户
create login '登录名'
with 
password='123456',
--must_change,
--sid=0x14585E90117152449347750164BA00A7,
default_database=master,
--default_language=language,
check_expiration=off,
check_policy=off
--credential=[sysadmin]

二、账户服务器角色授权

1.常用服务器角色类型

@rolename枚举值(角色权限)：
#1.bulkadmin            --可以运行BULK INSERT语句
#2.dbcreator            --可以创建、修改数据库    
#3.diskadmin            --用户管理磁盘文件
#4.processadmin         --可以终止SQL SERVER实例中的进程
#5.public               --默认且不可修改    
#6.securityadmin        --管理和审核登录账户
#7.serveradmin          --可以更改服务器范围的配置选项和关闭服务器
#8.setupadmin           --配置复制和链接服务器
#9.sysadmin             --执行任何活动

2.单独授权服务器角色

#1.声明引用数据库
USE [master] 

#2.创建登录名并授权服务器角色
EXEC master..sp_addsrvrolemember @loginame = N'登录名', @rolename = N'sysadmin'
EXEC master..sp_addsrvrolemember @loginame = N'登录名', @rolename = N'bulkadmin'
EXEC master..sp_addsrvrolemember @loginame = N'登录名', @rolename = N'dbcreator'
EXEC master..sp_addsrvrolemember @loginame = N'登录名', @rolename = N'diskadmin'
EXEC master..sp_addsrvrolemember @loginame = N'登录名', @rolename = N'processadmin'
EXEC master..sp_addsrvrolemember @loginame = N'登录名', @rolename = N'securityadmin'
EXEC master..sp_addsrvrolemember @loginame = N'登录名', @rolename = N'serveradmin'
EXEC master..sp_addsrvrolemember @loginame = N'登录名', @rolename = N'setupadmin'
EXEC master..sp_addsrvrolemember @loginame = N'登录名', @rolename = N'sysadmin'

3.删除服务器角色

#1.声明引用数据库
USE [master] 

#2.删除账户角色
EXEC master..sp_dropsrvrolemember @loginame = N'登录名', @rolename = N'sysadmin'
EXEC master..sp_dropsrvrolemember @loginame = N'登录名', @rolename = N'bulkadmin'
EXEC master..sp_dropsrvrolemember @loginame = N'登录名', @rolename = N'dbcreator'
EXEC master..sp_dropsrvrolemember @loginame = N'登录名', @rolename = N'diskadmin'
EXEC master..sp_dropsrvrolemember @loginame = N'登录名', @rolename = N'processadmin'
EXEC master..sp_dropsrvrolemember @loginame = N'登录名', @rolename = N'securityadmin'
EXEC master..sp_dropsrvrolemember @loginame = N'登录名', @rolename = N'serveradmin'
EXEC master..sp_dropsrvrolemember @loginame = N'登录名', @rolename = N'setupadmin'
EXEC master..sp_dropsrvrolemember @loginame = N'登录名', @rolename = N'sysadmin'

4.单用户添加多个服务器角色

#1.声明引用数据库
USE [master] 

#2.添加多个服务器角色，服务器角色用于向用户授权服务器范围内的安全特权
alter server role [bulkadmin] add member '登录名';
alter server role [dbcreator] add member '登录名';
alter server role [diskadmin] add member '登录名';
alter server role [processadmin] add member '登录名';
alter server role [securityadmin] add member '登录名';
alter server role [serveradmin] add member '登录名';
alter server role [setupadmin] add member '登录名';
alter server role [sysadmin] add member '登录名';

三、数据库授权

1.授权所有库

#1.声明引用数据库
USE [master] 

#2.授权所有库
EXEC master..sp_addsrvrolemember @loginame = N'登录名', @rolename = N'sysadmin'

2.访问特定数据库

#1.声明引用数据库
USE [master] 

#2.删除用户拥有的sysadmin角色
EXEC master..sp_dropsrvrolemember @loginame = N'登录名', @rolename = N'sysadmin'

#3.为登录用户创建数据库用户映射
USE databasename
create user [登录名] for login [登录名] with default_schema=dbo 

ps：此时还不可访问该数据库的对象如表、存储过程、视图等

#4.示例;
为登陆账户创建数据库用户（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.访问该数据库的所有对象

#1.声明引用数据库
USE databasename

#2.设置登录用户访问该数据库的所有对象
exec sp_addrolemember 'db_owner', '登录名'

4.禁用该数据库的所有对象

#1.声明引用数据库
USE databasename

#2.禁用登录用户访问该数据库的所有对象
exec sp_droprolemember 'db_owner', '登录名'

四、数据表、视图等授权

1.数据表、视图等授权分类

授权登录用户访问指定的表存储过程视图等(先禁用数据库用户拥有的db_owner角色，然后再对制定的对象赋相应的权限)

对象(表|存储过程|视图等)枚举值：
#1.ALTER              --修改
#2.CONTROL            --控制    
#3.EXECUTE            --执行
#4.TAKE OWNERSHIP     --所有权限
#5.VIEW DEFINITION    --查看定义

2.禁用该数据库的所有对象

#1.声明引用数据库
USE databasename

#2.禁用登录用户访问该数据库的所有对象
exec sp_droprolemember 'db_owner', '登录名'

3.授权登录用户访问指定的表存储过程视图等

#1.声明引用数据库
USE databasename

#授权登录用户访问指定的表、存储过程、视图等的授权
GRANT ALTER ON [dbo].[表|存储过程|视图等名称] TO [登录名]
GRANT CONTROL ON [dbo].[表|存储过程|视图等名称] TO [登录名]
GRANT EXECUTE ON [dbo].[表|存储过程|视图等名称] TO [登录名]
GRANT TAKE OWNERSHIP ON [dbo].[表|存储过程|视图等名称] TO [登录名]
GRANT VIEW DEFINITION ON [dbo].[表|存储过程|视图等名称] TO [登录名]

4.删除对登录用户访问指定的表存储过程视图等的授权

#1. 声明引用数据库
use databasename

#2.删除对登录用户访问指定的表存储过程视图等的授权
REVOKE ALTER ON [dbo].[表|存储过程|视图等名称] TO [登录名]
REVOKE CONTROL ON [dbo].[表|存储过程|视图等名称] TO [登录名]
REVOKE EXECUTE ON [dbo].[表|存储过程|视图等名称] TO [登录名]
REVOKE TAKE OWNERSHIP ON [dbo].[表|存储过程|视图等名称] TO [登录名]
REVOKE VIEW DEFINITION ON [dbo].[表|存储过程|视图等名称] TO [登录名]

5.授权登录用户访问表的指定列

#1. 声明引用数据库use databasename#2.授权登录用户访问表的指定列GRANT SELECT ON dbo.表名(字段1,字段2...) TO [登录名]

6.批量删除数据库所有表

#1. 声明引用数据库
use databasename

#2.批量删除数据库所有表
DECLARE @DROP_STRING VARCHAR(8000)

7.删除所有表的外键约束

#1.删除所有表的外键约束
DECLARE DROP_FK CURSOR FOR
SELECT  'ALTER TABLE '+ OBJECT_NAME(PARENT_OBJ) + ' DROP CONSTRAINT '+NAME
FROM    SYSOBJECTS
WHERE   XTYPE = 'F'
OPEN DROP_FK
FETCH NEXT FROM DROP_FK INTO @DROP_STRING
WHILE(@@FETCH_STATUS=0)
BEGIN     EXEC(@DROP_STRING)     FETCH NEXT FROM DROP_FK INTO @DROP_STRING
END
CLOSE DROP_FK
DEALLOCATE DROP_FK

8.删除表

DECLARE DROP_TABLE CURSOR FOR
SELECT  'DROP TABLE '+NAME
FROM    SYSOBJECTS
WHERE   XTYPE = 'U'
OPEN DROP_TABLE
FETCH NEXT FROM DROP_TABLE INTO @DROP_STRING
WHILE(@@FETCH_STATUS=0)
BEGIN     EXEC(@DROP_STRING)     FETCH NEXT FROM DROP_TABLE INTO @DROP_STRING
END
CLOSE DROP_TABLE
DEALLOCATE DROP_TABLE
GO

标签：#-#用户#数据库#密码#登录 | 浏览：684 | 发布日期：

免责声明：文章转载自《第二章 SQLserver常见用户授权配置》仅用于学习参考。如对内容有疑问，请及时联系本站处理。

上篇C#红绿状态灯”dpkg: 处理归档 /var/cache/apt/archives/XXXXXX(--unpack)时出错“的解决方法下篇

宿迁高防，2C2G15M，22元/月；香港BGP，2C5G5M，25元/月 雨云优惠码：MjYwNzM=

相关文章

最好用的数据库文档生成工具

一 前言 本文介绍一些比较流行的数据库文档生成工具，什么是数据库文档？ 其实在工作中进行项目交付的时候经常用到；简单来说数据库文档就是对整个数据库设计说明的文档，比如使用了哪个数据库，每张表的字段，类型说明等，然后整理成一个设计文档，这个文档多种多样，最常见的就是word，有了数据库文档对以后的数据库维护，或者进行二次开发都很方便； 二 流行的数据库文档生...

PostgreSQL操作-psql基本命令

一、命令说明psql是PostgreSQL的交互终端，等同于Oracle中的sqlplus。执行该命令连接数据库时, 默认的用户和数据库是postgres二、命令参数-c 命令 ：指定psql执行一条SQL命令(用双引号括起)，执行后退出。-d 数据库名 ：待连接的数据库名称。-f 文件名 ：使用文件中的数据作为命令的输入源，在处理完文件后，psql结束并...

二进制部署K8S-2集群部署

二进制部署K8S-2集群部署 感谢老男孩教育王导的公开视频，文档整理自https://www.yuque.com/duduniao/k8s。 因为在后期运行容器需要有大量的物理硬件资源使用的环境是用的Wmware EXSI如果使用笔记本可适当调整虚拟机配置。 角色 主机名 IP CPU Mem OS (主)负载均衡、内网DNS hdss7-11...

.net core ef 通过dbfirst方式连接sql server数据库

1. 创建基于.net core 的项目(过程略) 2. 使用nuget添加引用 Microsoft.EntityFrameworkCore Microsoft.EntityFrameworkCore.Tools Microsoft.EntityFrameworkCore.Design Microsoft.EntityFrameworkCore.SqlSe...

SQLServer数据库（二）

数据库设计：就是将数据库中的数据库实体及这些数据库实体之间的关系，进行规划和结构化的过程。 项目开发过程： 需求分析 概要设计 详细设计 代码编写 运行测试 打包发行 数据库的系统分析基本步骤：收集信息、标识实体、标识每个实体需要存储的详细信息、标识实体之间的关系。 实体，就是指现实世界中具有区分其它事物的特征或属性，并与其他实体有联系的实体。实体一般是名...

ubuntu apt 安装mysql 5.7

Ubuntu上安装MySQL非常简单只需要几条命令就可以完成。 1. sudo apt-get install mysql-server 2. apt-get isntall mysql-client 3. sudo apt-get install libmysqlclient-dev 安装过程中会提示设置密码什么的，注意设置了不要忘了，安装完成之后可以使...

最新文章

随机推荐

思享工具箱导航