SQL Server用户的设置与授权

SQL Server用户的设置与授权

SSMS 登陆方式有两种，一是直接使用Windows身份验证，二是SQL Server身份验证。使用SQL Server用户设置与授权不仅可以将不同的数据库开放给不同的用户外，而且可以提供一个通道给其他程序访问。

• 首先设置两个数据库，test和aaa。实现创建一个pc用户，仅仅授权它访问test的功能

• 先对SSMS设置否则其默认是不提供给任何用户访问的。1、对数据库实例的属性进行设置：右键“数据库服务器”---属性---安全性，设置服务器身份验证=“SQL Server 和 Windows身份验证模式”，设置登录审核=“失败和成功的登录”，点击确定。

• 在SQL数据库中进行如下查询：

--创建账户：
--登录账户名：pc	密码：123456
--默认连接到的数据库：test
create login pc with password='123456',default_database=[test];
--此时，pc账户可以连接到SQL Server服务器上，但此时还不能访问数据库中的对象
--严格说，此时pc账户默认是guest数据库用户身份，可以访问guest能够访问的数据库对象
 
--指定数据库用户pc的默认模式是dbo
--相当于用户pc执行select * from t，实际上执行的是select * from dbo.t
--是查询补加dbo.前缀是为了与低版本SQL Server导过来的表相同
create user pc for login pc with default_schema=[dbo];
 
--在test数据库中，通过加入数据库角色，赋予数据库用户“db_owner”权限
--此时pc可以随意操作数据库test
use [test];
go 
exec sp_addrolemember 'db_owner','pc';
go

该段SQL语句秉承微软数据库的所有传统，凡是涉及表名、数据库名都用 [ ] 表明，以免触发系统的关键字。

go 字段用于确保 use [test] 和 exec语句不分裂，否则 sp_adrolemember 这个SQL 保留的存储过程是无法执行的，找不到你所指定的数据库

关于dbo ：dbo默认用户也是架构，dbo作为架构是为了更好的与2000兼容，这样2000中的代码移植到2005中就不会报错，dbo 在2005中是默认的架构，创建表时，如不指定架构则默认属于dbo为架构

• 执行后查看结果如下。在SSMS对象资源管理器--安全性--登录名中多了用户 pc 查看其属性--用户映射，可以看到test被用户pc映射，且其身份是db_owner，意即数据库拥有者。

• 但此时还不能用pc账号登录SQL。在服务器属性--连接---设置允许远程连接，在SQL Server配置管理器中允许TCP/IP 和 Named Pipes

• 重启，使用之前在sql语句中设置的用户名pc 和密码123456，选择 SQL Server 验证方式登录。

• 登录该账户后默认登陆在数据库test中，如果你要试图打开其他未授权数据库或者使用use aaa;则会出现如下结果

• 删除账户。如果不需要这个pc账户了，可以在使用Windows身份验证登录SSMS后，执行以下代码，执行后pc就被删除了。

--删除数据库用户
drop user pc;
 
--删除 SQL Server 登录账户
drop login pc;

---

sa用户登录

安装SQL Server时设置了一个超级管理员 super administrator（sa），但在SQL Server中并不能像其他数据库的root账户一样直接用来登录。

若要使用 sa 来登录则必须进行如下设置：

• 设置 sa 的属性 允许登录

• 在属性--常规中，取消“强制实施密码策略”

• 使用sa账号和密码重新登录