Sql server 权限管理

1、设置某一登录账户只能看到部分数据库

参考 TN VIEW ANY DATABASE权限
通过sp_addlogin创建登录名
–basic:用户登陆账号
–basic123456:用户登陆密码

USE master
GO
execute sp_addlogin 'basic','basic123456'

–VIEW ANY DATABASE 权限控制是否显示 sys.databases 和 sys.sysdatabases 视图以及 sp_helpdb 系统存储过程中的元数据。
可用下面语句查询状态：

SELECT l.name as grantee_name, p.state_desc, p.permission_name 
FROM sys.server_permissions AS p JOIN sys.server_principals AS l 
ON   p.grantee_principal_id = l.principal_id
WHERE permission_name = 'VIEW ANY DATABASE' ;
GO

拒绝basic登录名访问任何数据库（看不到）
deny VIEW ANY DATABASE TO [basic]；
--GRANT VIEW ANY DATABASE TO <login> 授予特定登录帐户

若想被登录账户basic 看到某一数据库如： BasicData , 执行下面的语句：

ALTER AUTHORIZATION ON DATABASE::BasicData TO [basic]

–use BasData
–这一步骤执行不了，提示已经存在basic账号了。
–经过测试，将前面创建账号的步骤去掉，这一语句即可正常执行。
–在数据库BasicData里创建数据库basic账号
–第一个basic为登陆名
–第二个basic为数据库账号
–execute sp_grantdbaccess ‘basic’,’basic’
–将TestUser账号添加到数据库角色为数据库所有者
–execute sp_addrolemember ‘db_owner’,’basic’

2、赋予、回收表的权限

---授予用户person对表Person.Address的修改权限
USE AdventureWorks2008R2;
GRANT UPDATE ON Person.Address TO person;
GO

---授予用户person对表Person.Address的插入权限
USE AdventureWorks2008R2;
GRANT INSERT ON Person.Address TO person;
GO

---授予用户person对表Person.Address的删除权限
USE AdventureWorks2008R2;
GRANT DELETE ON Person.Address TO person;


 --授予用户存储过程dbo.prc_errorlog的执行权限
GRANT EXECUTE ON dbo.prc_errorlog TO person

--回收修改
USE AdventureWorks2008R2;
REVOKE update ON   Person.Address FROM person;

USE AdventureWorks2008R2;
REVOKE alter ON   Person.Address FROM person;

--回收删除
USE AdventureWorks2008R2;
REVOKE delete ON   Person.Address FROM person;

--回收查询
USE AdventureWorks2008R2;
REVOKE select ON   Person.Address FROM person;

3、如何设置对列的权限

一、方式一：使用视图

将需要限制用户只能看到特定的几个列、设置成一个视图，然后对这个视图进行权限控制

二、方式二：使用GRANT语句

1、授予相关列的查询权限（SELECT）

在数据库db1中，登录名UserA 只能有权限查询 Employee表 里面的BusinessEntityID, NationalIDNumber, LoginID三个字段权限，不能查询其它字段

GRANT SELECT(BusinessEntityID, NationalIDNumber, LoginID) ON Employee TO UserA

2、授予相关列的修改权限（Update）

对于登录名UserB，只允许其修改 Address表 的AddressLine1,AddressLine2两个字段，其它字段不许修改

GRANT UPDATE(AddressLine1,AddressLine2) ON Address TO UserB

4、查询权限

登入名表

select * from master.sys.syslogins

登入名与服务器角色关联表

select * from sys.server_role_members

服务器角色表

select * from sys.server_principals

查询登入名拥有的服务器角色

select SrvRole = g.name, MemberName = u.name, MemberSID = u.sid
from sys.server_role_members m inner join sys.server_principals g on g.principal_id = m.role_principal_id inner join sys.server_principals u on u.principal_id = m.member_principal_id

数据库用户表

select * from sysusers

数据库用户表角色关联表
select * from sysmembers

数据库角色表
select * from sys.database_principals

查询数据库用户拥有的角色
select ta.name as username,tc.name as databaserole from sysusers ta inner join sysmembers tb on ta.uid=tb.memberuid inner join sys.database_principals tc on tb.groupuid=tc.principal_id

查询当前数据库用户关联的登入名

 use AdventureWorks2008R2
  select ta.name as loginname,tb.name as databaseusername from master.sys.syslogins ta inner join sysusers tb on ta.sid=tb.sid 

如果将当前数据库还原到另一台服务器实例上,刚好那台服务器上也存在person登入用户，你会发现二者的sid不一样，
由于sid不一样,所以登入用户不具有当前数据库的访问权限,我们要想办法将二者关联起来。

关联登入名与数据库用户（将数据库用户的sid刷成登入名的sid）

   use AdventureWorks2008R2
    EXEC sp_change_users_login 'Update_One', 'person', 'person'
    Go

5、补充

USE [master]
GO
---创建登入名
CREATE LOGIN [person] WITH PASSWORD=N'person', DEFAULT_DATABASE=[news], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [news]
GO
---在指定的数据库下创建和登入名相关联的数据库用户
CREATE USER [person] FOR LOGIN [person]
GO
USE [news]
GO
---在指定的数据库下授予用户SELECT,DELETE,UPDATE,INSERT,EXECUTE权限。
GRANT SELECT,DELETE,UPDATE,INSERT,EXECUTE TO person;

参考 SQL Server 权限管理
over.