ITPub博客

首页 > 数据库 > SQL Server > Sql Server关于权限、角色以及登录名、用户名的总结

Sql Server关于权限、角色以及登录名、用户名的总结

原创 SQL Server 作者:lusklusklusk 时间:2020-07-31 19:59:08 0 删除 编辑

官方文档https://docs.microsoft.com/zh-cn/sql/t-sql/statements/grant-transact-sql?view=sql-server-2017


权限的一点总结
1、实例级别的角色是固定的,就是public、sysadmin、securityadmin、serveradmin、setupadmin、processadmin、diskadmin、dbcreator、bulkadmin
2、每个数据库拥有的角色不一样,msdb数据库中引入了SQLAgentUserRole、SQLAgentReaderRole、SQLAgentOperatorRole,而其他数据库并没有这几个角色
3、登录名是属于实例级别的CREATE LOGIN
4、用户名是属于数据库级别的CREATE USER
5、安装界面Specify SQL Server administratorss时增加的用户,使用SSMS图形界面打开实例时,显示在SSMS的Security--logins这一栏
6、权限是写在库里面的
6.1、在AG的辅助副本上对某个登录名授权时,根本无法授权报错数据库read_only,AG的主副本授权后,权限从AG主副本自动同步到了AG的辅助副本
6.2、两个实例A、B,两者上面有一样的登录名,A上的一个登录名user1拥有某个数据库DB1的owner权限,把DB1恢复到B上,发现B上的登录名user1也自动拥有了数据库DB1的owner权限,把B上DB1删除后,B上登录名user1没有了数据库DB1的owner权限,重新把DB1恢复到B上后,B上登录名user1又自动拥有了数据库DB1的owner权限。当然如果两个实例A、B上面没有一样的登录名,A上的登录名user1拥有某个数据库DB1的owner权限,把DB1恢复到B上,但是B上没有user1这个登录名,恢复也不报错,个人感觉恢复的过程中,其实执行了一个语句ALTER ROLE [db_owner] ADD MEMBER [user1],虽然B上没有user1,但是恢复过程也不受影响
7、登录名含有特殊字符时,在代码比如sql agert job的step步骤中使用该登录名时,使用[]中括号括起来,不要用""双引号括起来
8、某个登录名想要拥有某个数据库下执行sp的权限,只要该用户拥有该数据库的db_datareader角色和execute权限就可以了(use dbname;ALTER ROLE [db_datareader] ADD MEMBER [Domain\account];grant execute to [Domain\account];)
9、grant select to username拥有的权限不等于db_datareader角色拥有的权限
10、手工授权和SSMS图形界面有时是有区别的,比如SSMS图形界面某个登录名勾选了某个数据库的public权限,就说明这个用户有connect到这个数据库的权限,不等于use dbname;grant connect to username,因为图形界面登录名在某个数据库勾选了public,此时schema是dbo,而grant connect to username语句执行后,此时SSMS图形界面可以看到schema是username,之后不能在图形界面取消这个登录名在这个数据库的public权限,会报错the database principal owns a schema in the database,and cannot be dropped,只能执行revoke connect from username来取消
11、没有grant connect on dbname to username这样的操作
12、关于操作系统里面的用户或组,如果用户已经是OS系统的administrator组,则该用户直接拥有对所有DB的读写权限,因为这个用户在数据库里面隶属于BUILTIN\Administrators;如果用户隶属于数据库服务器的某个用户组,如用户A1隶属于数据库服务器用户组DA\DBA1,则DA\DBA1有的权限,A1也有;如何在数据库登录列表里面确定是用户还是组,看这个对象前面的图标,如果是一个人的小图标就是用户,如果是两个人的小图标就是组




登录名创建语法
CREATE LOGIN [Domain\sqlprocess] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

查询所有登录名(登录名是基于实例级别的,不是某个数据库级别的)
select * from sys.server_principals where type in ('U','G','S')

用户名创建语法
use dbname;
CREATE USER [Domain\sqlprocess] FOR LOGIN [Domain\sqlprocess] WITH DEFAULT_SCHEMA=[Domain\sqlprocess]

查询某个数据库下所有的用户名(用户名是基于某个数据库的)
SELECT * FROM testdb1.sys.sysusers where status<>0



图形界面添加登录名Domain\Wei并赋予该登录名具有某个数据库msdb的db_datareader权限时,其实是按顺序执行了如下三条语句
USE [master]
GO
CREATE LOGIN [Domain\Wei] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
USE [msdb]
GO
CREATE USER [Domain\Wei] FOR LOGIN [Domain\Wei]
GO
USE [msdb]
GO
ALTER ROLE [db_datareader] ADD MEMBER [Domain\Wei]
GO


查看某个数据下,哪些用户拥有哪些角色权限
USE DB;
WITH CTE AS
(
SELECT u.name AS UserName,
g.name AS dbRole,
'√' as 'flag'
FROM sys.database_principals u
INNER JOIN sys.database_role_members m ON u.principal_id = m.member_principal_id
INNER JOIN sys.database_principals g ON g.principal_id = m.role_principal_id
)
SELECT * FROM CTE PIVOT(MAX(flag) FOR dbRole IN ([public],
[db_owner],
[db_accessadmin],
[db_securityadmin],
[db_ddladmin],
[db_backupoperator],
[db_datareader],
[db_datawriter],
[db_denydatareader],
[db_denydatawriter])) as rg;





角色

查询实例级别的角色名称
select * from sys.server_principals where type='R'

查询某个数据库拥有的角色名称
select * from sys.database_principals where type='R'
每个数据拥有的角色是不一样的,比如msdb数据库中引入了SQLAgentUserRole、SQLAgentReaderRole、SQLAgentOperatorRole,而其他数据库并没有这几个角色

SQLAgentReaderRole对视图msdb.dbo.sysjobs_view有SELECT权限(继承SQLAgentUserRole的权限),GUI界面操作时从视图获取数据。所以添加到此角色后,展开作业就能返回所有作业。
我们用语句查询作业时,习惯直接从msdb.dbo.sysjobs这类表入手。但SQLAgentUserRole角色并没有对此类表有SELECT权限,因此常规语句会报拒绝对对象的SELECT权限。

查询某数据库下,角色拥有的成员信息(比如db_datareader这个角色,哪些登录名拥有了这个角色)

SELECT DP1.name AS DatabaseRoleName,   
   isnull (DP2.name, 'No members') AS DatabaseUserName   
 FROM sys.database_role_members AS DRM  
 RIGHT OUTER JOIN sys.database_principals AS DP1  
   ON DRM.role_principal_id = DP1.principal_id  
 LEFT OUTER JOIN sys.database_principals AS DP2  
   ON DRM.member_principal_id = DP2.principal_id  
WHERE DP1.type = 'R'
ORDER BY DP1.name;  


查看SQLServer用户哪些权限是使用grant命令操作赋予的
use dbname
exec sp_helprotect @username = 'username'


创建一个只读角色db_reader的操作
CREATE SERVER ROLE [db_reader];
GRANT VIEW ANY DATABASE TO [db_reader];
GRANT CONNECT ANY DATABASE TO [db_reader];
GRANT SELECT ALL USER SECURABLES TO [db_reader];



sqlserver为何本地administrator也无法登录的理解
原因:是因为安装数据库后,没有把administrator加入到sqlserver的登录用户中。
比如一台服务器名称为dbprod127,但是登录名里面并没有builtin\administrator和dbprod127\administrator,这样使用本地administrator登录操作系统后,再打开SSMS无法登录本地的sqlserver数据库


修改权限报错Cannot add the principal,incorrect syntax near 'XX'
ALTER ROLE [db_datareader] ADD MEMBER [Domain\wang]
报错:Cannot add the principal 'Domain\wang', because it does not exist or you do not have permission

解决方法,把代码修改为如下,增加user即可
IF NOT EXISTS ( SELECT TOP (1) 1 FROM sys.database_principals WHERE name = 'Domain\wang' )
BEGIN
    CREATE USER [Domain\wang] FOR LOGIN [Domain\wang]
END
GO
ALTER ROLE [db_datareader] ADD MEMBER [Domain\wang]


登录名含有特殊字符时,使用[]中括号括起来,不要用""双引号括起来
在job里面的代码直接写成如下会报错:incorrect syntax near 'Domain\wang'
grant execute to "Domain\j.wang"

修改成如下即可
grant execute to [Domain\j.wang]


删除登录名报错及对应解决方法
DROP LOGIN [Domain\user1]
报错:Server principal 'Domain\user1' has granted one or more permission(s). Revoke the permission(s) before dropping the server principal.
解决方法
SELECT b.name as Grantor, c.name as Grantee, a.state_desc as PermissionState
, a.class_desc as PermissionClass, a.type as PermissionType
, a.permission_name as PermissionName, a.major_id as SecurableID
FROM sys.server_permissions a JOIN sys.server_principals b
ON a.grantor_principal_id = b.principal_id
JOIN sys.server_principals c
ON a.grantee_principal_id = c.principal_id
WHERE grantor_principal_id =
(SELECT principal_id FROM sys.server_principals WHERE name = 'Domain\user1')
--Domain\user1拥有ENDPOINT的权限
select * from  sys.endpoints where endpoint_id=上面的a.major_id
--查询该用户ENDPOINT对应的具体名称, 原来是mirror
ALTER AUTHORIZATION ON ENDPOINT::mirroring TO [Domain\userXX]
--修改mirror的权限给其他用户Domain\userXX
DROP LOGIN [Domain\user1]
--正常删除了



数据库的角色

public
--public 角色是一个特殊的数据库角色,每个数据库用户都属于它。public 角色:
--捕获数据库中用户的所有默认权限。
--无法将用户、组或角色指派给它,因为默认情况下它们即属于该角色。
--含在每个数据库中,包括 master、msdb、tempdb、model 和所有用户数据库。
--无法除去。

db_owner
--进行所有数据库角色的活动,以及数据库中的其它维护和配置活动。
--该角色的权限跨越所有其它固定数据库角色。

db_accessadmin
--在数据库中添加或删除 Windows NT 4.0 或 Windows 2000 组和用户以及 SQL Server 用户。

db_datareader
--查看来自数据库中所有用户表的全部数据。

db_datawriter
--添加、更改或删除来自数据库中所有用户表的数据

db_ddladmin
--添加、修改或除去数据库中的对象(运行所有 DDL)

db_securityadmin
--管理 SQL Server 2000 数据库角色的角色和成员,并管理数据库中的语句和对象权限

db_backupoperator
--有备份数据库的权限

db_denydatareader
--拒绝选择数据库数据的权限

db_denydatawriter
--拒绝更改数据库数据的权限

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30126024/viewspace-2708510/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Welcome to Lukes DB HOME。 Oracle OCM、Mysql OCP, 8年以上DBA工作经验,博客仅记录自己的一个学习过程,不代表完全准确,如有需要,欢迎转载。

注册时间:2015-02-02

  • 博文量
    417
  • 访问量
    690895