权限赋予

MSSQL中权限存在三种方式

1、login设置server roles

2、user设置user roles

3、user通过gant/deny对DBobject进行一一权限授予或拒绝

第一种方式一般我们在给予一个login高权限时使用，比如我们要授予一个用户sa级别权限可以使用

EXEC master..sp_addsrvrolemember @loginame = N'LoginName', @rolename = N'sysadmin'

第二种方式是最常用的授权方式，该方式统一简洁容易管理，是推荐的办法。

如下面，我们给予了用户对当前DB 的读写权限

use DBName                             
go
IF not EXISTS(
	SELECT TOp 1 *
	FROM sys.sysusers
	WHERE name = 'UserName'
)
BEGIN
	EXEC('create user UserName   for login LoginName  ')
	EXEC('EXEC sp_addrolemember ''db_datareader'',UserName  ')
end

常用role为

db_datareader --对当前数据库对象的只读权限

db_datawriter --对当前数据库对象的写权限

db_owner --数据库所有者，对当前DB对象的全部操作权限

我们可以通过一下脚本查看当前DB中所有用户拥有的对象，以及对应的LoginName

use DBName 
go
select  DBRole = g.name, MemberName = u.name,LoginName=t1.name
 From sys.database_principals u with(nolock), 
      sys.database_principals g with(nolock), 
      sys.database_role_members m with(nolock),
	  sys.server_principals t1 with(nolock)
 Where g.principal_id = m.role_principal_id And u.principal_id = m.member_principal_id and 
	u.sid=t1.sid 

该方式存在的缺陷为，除了db_owner外，其他角色并不能得到执行sp/funcation的权限，而给予db_owner用户权限又会大。

第三种方式也是一种常见的授权方式 ，但一般都仅正对需要对单独DB 对象做权限控制的时候时候 ，比如同步链的目的端就应该禁止管理员以外的人手动更改，否则会出现同步错误，这是该方法就发挥作用了

--允许/拒绝表的单独权限给用户
grant/DENY INSERT,UPDATE,DELETE ON TBName TO UserName
--给予用户某个具体的SP运行权限
grant execute on SPName to UserName
--给予用户对所有对象的查询权限
grant select to UserName

第二 和第三种方法结合使用我们可以解决仅允许用户查询或运行SP 这样的权限需求

--例如： 
--step 1
IF not EXISTS(
SELECT TOp 1 *
FROM sys.sql_logins
WHERE name = 'LoginName'
)
BEGIN
    CREATE LOGIN LoginName WITH PASSWORD = N'pwd',CHECK_policy = OFF
end
--step 2
use DBName                             
go
IF not EXISTS(
    SELECT TOp 1 *
    FROM sys.sysusers
    WHERE name = 'UserName'
)
begin
create user userName for login  LoginName
EXEC sp_addrolemember 'db_datareader',UserName
end

--step3
grant execute to userName