Sql server 权限管理

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

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

USE masterGOexecute 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_idWHERE 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、查询权限

登入名表

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

4、补充

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

参考 SQL Server 权限管理
over.