SQL Server 权限管理

USE AdventureWorks2008R2;
GRANT SELECT ON OBJECT::Person.Address TO person;
--或者使用
USE AdventureWorks2008R2;
GRANT SELECT ON Person.Address TO RosaQdM;
GO

---授予用户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;

标量函数权限：EXECUTE、REFERENCES。

表值函数权限：DELETE、INSERT、REFERENCES、SELECT、UPDATE。

存储过程权限：EXECUTE。

表权限：DELETE、INSERT、REFERENCES、SELECT、UPDATE。

视图权限：DELETE、INSERT、REFERENCES、SELECT、UPDATE。

---创建架构persons的表
CREATE TABLE Persons.sutdent
(id int not null)

---创建视图
USE AdventureWorks2008R2
GO
CREATE VIEW Persons.vwsutdent
AS
SELECT * FROM Persons.sutdent

GO
USE AdventureWorks2008R2
GO
---创建存储过程
CREATE PROCEDURE Persons.SP_sutdent
(@OPTION NVARCHAR(50))
AS
BEGIN
    SET NOCOUNT ON
    IF @OPTION='Select'
    BEGIN
    SELECT * FROM Persons.sutdent
    END
END
 

  ---登入名表
  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

exec sp_helprotect @username = 'person'

USE AdventureWorks2008R2;
REVOKE EXECUTE ON dbo.prc_errorlog    FROM 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;

USE AdventureWorks2008R2;
REVOKE CREATE TABLE FROM person;
GO


CONNECT权限是用户访问数据库的权限，将此权限回收后用户将无法访问数据库
--USE AdventureWorks2008R2;
--REVOKE CONNECT FROM person;
--GO

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;