用户及权限管理

1.实现以下操作：

1) 创建windows登录用户students

• 建Windows的用户。以管理员身份登录到Windows ，打开“控制面板”,在管理账户中添加新的账户students。

• 可以看出已经成功添加该账户

• 将Windows账户加入到SQL Server中。以管理员身份登录到SQL Server Management Studio，在“对象资源管理器”中，展开“安全性”节点，并右击“登录名”项，选择“新建登录名”菜单项，打开“登录名-新建”窗口。如图所示，可以通过单击“常规”选项卡的“搜索”按钮，在“选择用户或组”对话框中选择相应的用户名或用户组添加到SQL Server登录用户列表中

• 可以看出已经创建登录名为students的windows登录用户

• 也可以使用代码创建（前提是要先创建账户）

EXEC sp_grantlogin '2-130166\students';

2) 为windows用户students创建访问姓名+学号的用户账号，并设置students具有public身份

EXEC sp_addrolemember 'public','2-130166\students';

也可以不用代码直接用企业管理器操作

3）修改SQL Server实例为混合验证模式（即支持Windows身份验证和 SQL Server验证）

4) 创建SQL Server验证的用户teachers,(密码自定)并设置teachers具有姓名+学号的db_owner身份及db_accessadmin身份

• 在 "对象资源管理器" 中，右键单击 "安全性" -> "新建登录名",选择 "SQL Server 身份验证"，并设置登录名为teachers，输入密码，并确认密码。​
• 在 "用户映射" 选项卡中，选择要授予 teachers 用户访问权限的数据库，为 teachers 用户分配 db_owner 和 db_accessadmin 角色。
• 也可以使用代码来完成

CREATE LOGIN [teachers] WITH PASSWORD = '你自己设置的密码';
USE 你自己设置的数据库名称
CREATE USER [teachers] FOR LOGIN [teachers];
EXEC sp_addrolemember 'db_owner', 'teachers';
EXEC sp_addrolemember 'db_accessadmin', 'teachers';

• 可以看见自己创建的teachers

5) 创建SQL Server验证的用户testuser1和testuser2,并设置它们具有姓名+学号的public身份

EXEC sp_addlogin 'testuser1','123456'
EXEC sp_addlogin 'testuser2','123456'

之后在登录名右键点击属性，设置在数据库中public身份即可。

2.以teacher用户身份登录，完成以下操作

1) 授予用户testuser1只可以在姓名+学号中创建视图和表的权限

• 在数据库属性找到用户testuser1，选择相应权限

 

• 也可以使用代码来完成

USE 你自己设置的数据库名称
GRANT CREATE TABLE TO testuser1
GRANT CREATE VIEW TO testuser1

2) 授予用户testuser2不允许在姓名+学号中创建视图和表，但允许其它操作

• 在数据库属性找到用户testuser2，选择相应权限

• 也可以使用代码来完成

USE 你自己设置的数据库名称
DENY CREATE TABLE TO testuser2
DENY CREATE VIEW TO testuser2

3) 授予用户testuser1可以在姓名+学号中对S表的insert,update权限

GRANT INSERT ON dbo.s TO testuser1
GRANT UPDATE ON dbo.s TO testuser1
GO
sp_helprotect @username = 'testuser1'

其中使用sp_helprotect命令如果提供username参数，则仅报告该用户对数据库对象的权限，具体是以下内容：

• Object name：受保护对象的名称，如表、视图、存储过程等。
• Owner name：受保护对象的所有者名称，通常为数据库用户。
• Protection type：保护类型，即授权类型。
• Action：可执行的操作。
• Column name：受保护对象的列名称。
• Grantor：授权者的名称。
• Grantee：受保护者的名称。
• Grantor type：授权者类型，可以是 Windows 用户、Windows 组或 SQL Server 角色。
• Grantor control：授权者对权限的控制级别。
• Grantee type：受保护者类型，可以是 Windows 用户、Windows 组或 SQL Server 角色。
• Grantee control：受保护者对权限的控制级别。

从表中可以看出用户testuser1对s表的insert,update权限。

4) 授予用户testuser2可以在姓名+学号中对S表的insert权限，并废除对表S的Update权限

GRANT INSERT ON dbo.s TO testuser2
DENY UPDATE ON dbo.s TO testuser2
GO
sp_helprotect @username = 'testuser2'

5) 授予用户testuser1可以在姓名+学号中对S表的列sno,sn的select权限,对列sn的update权限

GRANT SELECT ON dbo.s(sno) 
TO testuser1
GRANT SELECT ON dbo.s(sn) 
TO testuser1
GRANT UPDATE ON dbo.s(sn) 
TO testuser1
GO
sp_helprotect @username = 'testuser1'

 6) 分别以testuser1,testuser2登录，并测试其权限是否生效

• 用testuser1登录，测试查询sno,sn的权限，但由于用户testuser1没有s表其他列的查询权限，因此会失败。
• 用testuser2登录，测试插入权限成功。

3.以管理员用户身份登录，完成以下操作

1) 创建角色testrole, 授予testrole可以在姓名+学号中对表sc具有update权限

exec sp_addrole testrole
grant update on sc to testrole;

2) 将用户testuser2充当testrole角色

EXEC sp_addrolemember 'testrole','testuser2';

结果如下：

• 查看数据库角色属性也可以看出已经成功建立。

3) 以testuser2登录，并测试其权限是否生效

• 测试用户testuser2对表sc的update权限