一、 用户
1. 孤立用户
孤立用户即数据库中有而服务器层没有的用户（数据库中未链接到任何sqlserver登录名的用户）
检测当前数据库中的孤立用户
sp_change_users_login @Action='Report';重新链接数据库与服务器层用户
sp_change_users_login @Action='update_one',@UserNamePattern='db_user',@LoginName='server_user_name'相关视图
sys.server_principals
sys.database_principals
二、 权限
查询在服务器级别授予或拒绝的权限（只能在master库中执行）
SELECT pr.type_desc,pr.name,
isnull (pe.state_desc, 'No permission statements') AS state_desc,
isnull (pe.permission_name, 'No permission statements') AS permission_name
FROM sys.server_principals AS pr
LEFT OUTER JOIN sys.server_permissions AS pe
ON pr.principal_id = pe.grantee_principal_id
WHERE is_fixed_role = 0 -- Remove for SQL Server 2008
ORDER BY pr.name, type_desc;查询在数据库级别授予或拒绝的权限列表（只能在每个数据库中执行）
SELECT pr.type_desc, pr.name,
isnull (pe.state_desc, 'No permission statements') AS state_desc,
isnull (pe.permission_name, 'No permission statements') AS permission_name,object_name(pe.major_id)
FROM sys.database_principals AS pr
LEFT OUTER JOIN sys.database_permissions AS pe
ON pr.principal_id = pe.grantee_principal_id
WHERE pr.is_fixed_role = 0 and name='ProductCenterSA'
ORDER BY pr.name, type_desc;查询所有用户的对象级权限
SELECT pr.type_desc, pr.name, pe.state_desc,
pe.permission_name, s.name + '.' + oj.name AS Object, major_id
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
ON pr.principal_id = pe.grantee_principal_id
JOIN sys.objects AS oj
ON oj.object_id = pe.major_id
JOIN sys.schemas AS s
ON oj.schema_id = s.schema_id
WHERE class_desc = 'OBJECT_OR_COLUMN';查询指定用户权限
exec sp_helprotect @username ='Reader'查询当前用户权限
select * from sys.fn_my_permissions(null,'server');
select * from sys.fn_my_permissions(null,'database');确定某个特定用户是否拥有某个权限
EXECUTE AS USER = 'TestUser';
SELECT HAS_PERMS_BY_NAME ('dbo.T1', 'OBJECT', 'SELECT');
REVERT;汇总版
--==================================================================================================================
-- ScriptName : get_login_rights_script.sql
-- Author : 潇湘隐者
-- CreateDate : 2015-12-18
-- Description : 查看某个登录名被授予的数据库对象的权限的脚本（授权脚本和回收权限脚本）
-- Note :
/*******************************************************************************************************************
Parameters : 参数说明
********************************************************************************************************************
@login_name : 你要查看权限的登录名(需要输入替换的参数)
********************************************************************************************************************
Notice ： 由于系统视图的缺陷，此脚本无法显示服务器角色public、数据库角色public
********************************************************************************************************************
Modified Date Modified User Version Modified Reason
********************************************************************************************************************
2018-08-03 潇湘隐者 V01.00.00 新建该脚本。
2019-04-04 潇湘隐者 V01.01.00 Fix掉一个bug，某个表只允许更新某个字段，但是这里显示更新整个表。
2019-09-25 潇湘隐者 V01.02.00 解决只能查看某个用户数据库，不能查看所有数据库的权限问题。
2019-09-25 潇湘隐者 V01.03.00 解决数据库名包含中划线[-], 出现下面错误问题
2019-09-26 潇湘隐者 V01.04.00 解决系统表和系统视图大小写问题（排序规则区分大小时，会报错）
2019-09-26 潇湘隐者 V01.04.00 加入数据库角色详细信息
2019-11-22 潇湘隐者 V01.04.00 解决SQL不能查询到授予的服务器级权限的Bug
*******************************************************************************************************************/
--==================================================================================================================
DECLARE @login_name NVARCHAR(32)= 'test';
DECLARE @database_name NVARCHAR(64);
DECLARE @cmdText NVARCHAR(MAX);
IF OBJECT_ID('TempDB.dbo.#databases') IS NOT NULL
DROP TABLE dbo.#databases;
CREATE TABLE #databases
(
database_id INT,
database_name sysname
);
IF OBJECT_ID('tempdb.dbo.#user_db_roles') IS NOT NULL
DROP TABLE dbo.#user_db_roles;
--CREATE TABLE dbo.#user_db_roles
--(
-- [DB_NAME] NVARCHAR(64)
-- ,[USER_NAME] NVARCHAR(64)
-- ,[ROLE_NAME] NVARCHAR(64)
--);
CREATE TABLE dbo.#user_db_roles
(
[DB_NAME] NVARCHAR(64)
,[USER_NAME] NVARCHAR(64)
,[ROLE_NAME] NVARCHAR(64)
,[PRINCIPAL_TYPE_DESC] NVARCHAR(64)
,[CLASS_DESC] NVARCHAR(64)
,[PERMISSION_NAME] NVARCHAR(64)
,[OBJECT_NAME] NVARCHAR(128)
,[PERMISSION_STATE_DESC] NVARCHAR(128)
);
IF OBJECT_ID('tempdb.dbo.#user_object_rights') IS NOT NULL
DROP TABLE dbo.#user_object_rights;
CREATE TABLE dbo.#user_object_rights
(
[DATABASE_NAME] NVARCHAR(128),
[SCHEMA_NAME] NVARCHAR(64),
[OBJECT_NAME] NVARCHAR(128),
[USER_NAME] NVARCHAR(32),
[PERMISSIONS_TYPE] CHAR(12),
[PERMISSION_NAME] NVARCHAR(128),
[PERMISSION_STATE] NVARCHAR(64),
[CLASS_DESC] NVARCHAR(64),
[COLUMN_NAME] NVARCHAR(32),
[STATE_DESC] NVARCHAR(64),
[GRANT_STMT] NVARCHAR(MAX),
[REVOKE_STMT] NVARCHAR(MAX)
)
INSERT INTO #databases
SELECT database_id ,
name
FROM sys.databases
WHERE name NOT IN ('model') AND state = 0; --state_desc=ONLINE
--登录名授予的服务器角色
SELECT UserName = u.name ,
ServerRole = g.name ,
Type = u.type,
Type_Desc = u.Type_Desc,
Create_Date = u.create_date,
Modify_Date = u.modify_date,
DenyLogin = l.denylogin
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
INNER JOIN sys.syslogins l ON u.name = l.name
WHERE l.name=@login_name
ORDER BY u.name,g.name;
--登录名授予的服务器级权限
SELECT grantor_principal.name AS [Grantor] ,
prmssn.state AS [PermissionState] ,
prmssn.state_desc AS [PermissionStateDesc],
prmssn.type AS [PermissionCode] ,
prmssn.permission_name AS [PermissionName]
FROM sys.server_permissions AS prmssn
INNER JOIN sys.server_principals AS grantor_principal ON grantor_principal.principal_id = prmssn.grantor_principal_id
INNER JOIN sys.server_principals AS grantee_principal ON grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE grantee_principal.name = @login_name
ORDER BY [PermissionName] DESC;
WHILE 1= 1
BEGIN
SELECT TOP 1 @database_name= database_name
FROM #databases
ORDER BY database_id;
IF @@ROWCOUNT =0
BREAK;
SET @cmdText = N'USE ' + QUOTENAME(@database_name) + N';' +CHAR(10)
--登录名授予的数据库角色
/********************************************************************************
SELECT @cmdText += N'INSERT INTO #user_db_roles
SELECT DB_NAME() AS [DB_NAME]
,M.NAME AS [USER_NAME]
,R.NAME AS [ROLE_NAME]
FROM sys.database_role_members RM
INNER JOIN sys.database_principals R ON RM.ROLE_PRINCIPAL_ID = R.PRINCIPAL_ID
INNER JOIN sys.database_principals M ON RM.MEMBER_PRINCIPAL_ID = M.PRINCIPAL_ID
WHERE M.NAME=@p_login_name' + CHAR(10);
EXEC SP_EXECUTESQL @cmdText, N'@p_login_name NVARCHAR(32)',@p_login_name=@login_name;
***********************************************************************************/
SELECT @cmdText += N'INSERT INTO #user_db_roles
SELECT DB_NAME() AS [DB_NAME] ,
u.name AS [USER_NAME] ,
r.name AS [ROLE_NAME] ,
t.[PRINCIPAL_TYPE_DESC] ,
t.[CLASS_DESC] ,
t.[PERMISSION_NAME] ,
t.[OBJECT_NAME] ,
t.PERMISSION_STATE_DESC
FROM sys.database_role_members AS m
INNER JOIN sys.database_principals AS r ON r.principal_id = m.role_principal_id
INNER JOIN sys.database_principals AS u ON u.principal_id = m.member_principal_id
LEFT JOIN ( SELECT USER_NAME(p.grantee_principal_id) AS principal_name ,
dp.type_desc AS PRINCIPAL_TYPE_DESC ,
p.class_desc AS CLASS_DESC ,
p.permission_name AS [PERMISSION_NAME] ,
OBJECT_NAME(p.major_id) AS [OBJECT_NAME] ,
p.state_desc AS [PERMISSION_STATE_DESC]
FROM sys.database_permissions p
INNER JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
) t ON t.principal_name = r.name
WHERE u.name = @p_login_name;' + CHAR(10);
EXEC SP_EXECUTESQL @cmdText, N'@p_login_name NVARCHAR(32)',@p_login_name=@login_name;
SET @cmdText = N'USE ' +QUOTENAME(@database_name) + N';' +CHAR(10);
--查看具体对象的授权问题
SELECT @cmdText +=N'INSERT INTO dbo.#user_object_rights
( [DATABASE_NAME] ,
[SCHEMA_NAME] ,
[OBJECT_NAME] ,
[USER_NAME] ,
[PERMISSIONS_TYPE] ,
[PERMISSION_NAME] ,
[PERMISSION_STATE] ,
[CLASS_DESC] ,
[COLUMN_NAME] ,
[STATE_DESC] ,
[GRANT_STMT] ,
[REVOKE_STMT]
)
SELECT DB_NAME() AS [DATABASE_NAME]
, sys.schemas.NAME AS [SCHEMA_NAME]
, ob.NAME AS [OBJECT_NAME]
, sys.database_principals.NAME AS [USER_NAME]
, dp.TYPE AS [PERMISSIONS_TYPE]
, dp.PERMISSION_NAME AS [PERMISSION_NAME]
, dp.STATE AS [PERMISSION_STATE]
, dp.CLASS_DESC AS [CLASS_DESC]
, sc.name AS [COLUMN_NAME]
, dp.STATE_DESC AS [STATE_DESC]
, dp.STATE_DESC + '' '' + dp.PERMISSION_NAME + '' ON [''+ sys.schemas.NAME + ''].['' + ob.NAME + ''] TO ['' + sys.database_principals.NAME + ''];'' COLLATE LATIN1_GENERAL_CI_AS
AS [GRANT_STMT]
, ''REVOKE '' + dp.PERMISSION_NAME + '' ON [''+ sys.schemas.NAME + ''].['' + ob.NAME + ''] FROM ['' + sys.database_principals.NAME + ''];'' COLLATE LATIN1_GENERAL_CI_AS
AS [REVOKE_STMT]
FROM sys.database_permissions dp
LEFT OUTER JOIN sys.objects ob ON dp.MAJOR_ID = ob.OBJECT_ID
LEFT OUTER JOIN sys.schemas ON ob.SCHEMA_ID = sys.schemas.SCHEMA_ID
LEFT OUTER JOIN sys.database_principals ON dp.GRANTEE_PRINCIPAL_ID = sys.database_principals.PRINCIPAL_ID
LEFT OUTER JOIN sys.columns sc ON ob.object_id = sc.object_id AND sc.column_id = dp.minor_id
WHERE sys.database_principals.NAME =@p_login_name
ORDER BY PERMISSIONS_TYPE;'
--PRINT(@cmdText);
EXEC SP_EXECUTESQL @cmdText, N'@p_login_name NVARCHAR(32)',@p_login_name=@login_name;
DELETE FROM #databases WHERE database_name=@database_name;
END
SELECT * FROM tempdb.dbo.#user_db_roles ORDER BY DB_NAME;
SELECT * FROM tempdb.dbo.#user_object_rights ORDER BY DATABASE_NAME;
IF OBJECT_ID('TempDB.dbo.#databases') IS NOT NULL
DROP TABLE dbo.#databases;
IF OBJECT_ID('tempdb.dbo.#user_db_roles') IS NOT NULL
DROP TABLE dbo.#user_db_roles;
IF OBJECT_ID('tempdb.dbo.#user_object_rights') IS NOT NULL
DROP TABLE dbo.#user_object_rights;
三、 对象间依赖关系
1. 图形界面
选中所需对象 -> 右键 -> 查看依赖关系 -> 可以选择依赖于该表/视图的对象 或 该表/视图依赖的对象
2. sql语句
查询某表/视图依赖的对象（单层）
SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name,
referenced_entity_name
FROM sys.sql_expression_dependencies AS sed
WHERE referencing_id = OBJECT_ID(N'对象名');
--从SQL Server 2008开始用的系统函数，被我引用的对象
SELECT * FROM sys.dm_sql_referenced_entities('dbo.SP1','OBJECT')级联查询
WITH objectCTE(referencing_entity_name,referenced_entity_name,level)
AS
( -- Anchor Member (AM)
select OBJECT_NAME(referencing_id) AS referencing_entity_name,referenced_entity_name,0 as level from sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID(N'对象名')
UNION ALL
-- Recursive Member (RM)
SELECT OBJECT_NAME(m.referencing_id),M.referenced_entity_name,level+1
FROM objectCTE AS E
JOIN sys.sql_expression_dependencies AS M
ON e.referenced_entity_name= OBJECT_NAME(m.referencing_id)
)
SELECT * FROM objectCTE查询依赖于某表/视图的对象（单层）
SELECT referenced_entity_name,OBJECT_NAME(referencing_id) AS referencing_entity_name
FROM sys.sql_expression_dependencies AS sed
WHERE referenced_entity_name = '对象名';
--从SQL Server 2008开始用的系统函数，引用我的对象
SELECT * FROM sys.dm_sql_referencing_entities('dbo.V1','OBJECT')级联查询
WITH objectCTE(referenced_entity_name,referencing_entity_name,level)
AS
( -- Anchor Member (AM)
select
referenced_entity_name,OBJECT_NAME(referencing_id) AS referencing_entity_name,0 as level from sys.sql_expression_dependencies
WHERE referenced_entity_name='Base_ItemEAN'
UNION ALL
-- Recursive Member (RM)
SELECT M.referenced_entity_name,OBJECT_NAME(m.referencing_id),level+1
FROM objectCTE AS E
JOIN sys.sql_expression_dependencies AS M
ON e.referencing_entity_name= M.referenced_entity_name
)
SELECT * FROM objectCTE3. 外键关系
--检查外键
select object_name(constraint_object_id) constraint_name,
object_name(parent_object_id) parent_object_name,
col_name(parent_object_id,parent_column_id) parent_object_column_name,
object_name(referenced_object_id) referenced_object_name,
col_name(referenced_object_id,referenced_column_id) referenced_object_column_name
from sys.foreign_key_columns
where referenced_object_id = object_id('tb1')4. 表上的索引、触发器
--检查索引
select object_name(object_id) as table_name,*
from sys.indexes
where name = 'IX_001' and object_id = object_id('T','U')
--检查DML触发器
select name as table_name, object_name(a.parent_obj) as dml_trigger_name
from sysobjects a
where a.xtype = 'TR'sys.dm_sql_referenced_entities 还可以查看被数据库/服务器DDL触发器引用的对象；
SELECT * FROM sys.dm_sql_referenced_entities ('ddl_database_trigger_name', 'DATABASE_DDL_TRIGGER');sys.dm_sql_referencing_entities 还可以查看引用了类型/分区函数等的对象。
5. 临时对象
对于存储过程中用到的临时表，只能检查到create table创建的非#开头临时表，并且用函数检查还会报错，因为表事先并不存在。
USE DB1
--只能检查到create table创建的非#临时表
SELECT schema_name(o.schema_id) as schema_name, o.name as object_name, o.type_desc,
d.referenced_server_name, d.referenced_database_name, isnull(d.referenced_schema_name,'dbo') as referenced_schema_name, d.referenced_entity_name
FROM sys.sql_expression_dependencies d
INNER JOIN sys.objects o
ON d.referencing_id = o.object_id
--并且用函数检查还会报错，因为表事先并不存在
select * from sys.dm_sql_referenced_entities('dbo.SP5','OBJECT');
/*
Msg 2020, Level 16, State 1, Line 4
The dependencies reported for entity "dbo.SP5" might not include references to all columns. This is either because the entity references an object that does not exist or because of an error in one or more statements in the entity. Before rerunning the query, ensure that there are no errors in the entity and that all objects referenced by the entity exist.
*/
6. 动态SQL里引用的对象
动态SQL里引用的对象，无论系统视图/函数，都查不到；也许只能试试查可编程对象的文本定义：
--ANSI SQL标准里定义的INFORMATION_SCHEMA对象
select * from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_DEFINITION like '%T2%'
--SQL Server 2000沿用下来的可编程对象文本定义
select * from syscomments
where text like '%T2%'
--SQL Server 2005开始的可编程对象文本定义
select * from sys.sql_modules
where definition like '%T2%'注意：这种方法，对于hard coding的对象名，非常好用，但是，
(1) 有时动态SQL里的对象名称并不是hard coding，所以也不一定能找到；比如：
EXEC('SELECT * FROM dbo.table' + '_name')
EXEC('SELECT * FROM ' + @table)(2) 另外一些书写不严格的SQL，也无法定位到对象名，比如：
SELECT * FROM dbo . table_name --这语法竟然也能通过
SELECT * FROM dbo.table_name_2 --名字只是部分类似，table_name_2不是table_name
四、 跨数据库/服务器对象
从SQL Server 2008开始，跨数据库，跨服务器引用的对象，已经可以查询。但是写法上要稍微调整下，因为当前数据库中，并没有其他数据库对象的object_id，所以不能按照object_id来关联。
USE DB1
SELECT schema_name(o.schema_id) as schema_name, o.name as object_name, o.type_desc,
d.referenced_server_name, d.referenced_database_name, isnull(d.referenced_schema_name,'dbo') as referenced_schema_name, d.referenced_entity_name
FROM sys.sql_expression_dependencies d
INNER JOIN sys.objects o
ON d.referencing_id = o.object_id
参考
https://docs.microsoft.com/zh-cn/sql/relational-databases/views/get-information-about-a-view?view=sql-server-2017
https://docs.microsoft.com/zh-cn/sql/relational-databases/tables/view-the-dependencies-of-a-table?view=sql-server-2017#TsqlProcedure
4212
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
