SQLServer 服务器角色public和数据库角色public权限回收

SQLServer 服务器角色public和数据库角色public默认授予每个账号和用户，并且不能禁用，该角色可以查看数据库中很多元数据，存在安全隐患，可使用以下脚本将角色的所有权限撤销。 注意：如果权限都撤销了，使用该账户登录ssms，界面将看不到任何对象了。

--	查看服务器角色 public 权限
SELECT sp1.type_desc AS grantor_type_desc,sp2.name AS grantee_name,sp2.type_desc AS grantee_type_desc,spe.class_desc,spe.state_desc,spe.permission_name,sp1.name AS [ObjectName]
FROM sys.server_permissions AS spe
INNER JOIN sys.server_principals AS sp1 ON sp1.principal_id = spe.major_id and spe.class = 101
INNER JOIN sys.server_principals AS sp2 ON sp2.principal_id = spe.grantee_principal_id
WHERE(sp2.name=N'public')
UNION
SELECT '' AS grantor_type_desc,c.name AS [ObjectName]
FROM sys.server_permissions AS spe
INNER JOIN sys.certificates AS c ON c.certificate_id = spe.major_id and spe.class = 106
INNER JOIN sys.server_principals AS sp2 ON sp2.principal_id = spe.grantee_principal_id
WHERE(sp2.name=N'public')
UNION
SELECT e.type_desc AS grantor_type_desc,e.name AS [ObjectName]
FROM sys.server_permissions AS spe
INNER JOIN sys.endpoints AS e ON e.endpoint_id = spe.major_id and spe.class = 105
INNER JOIN sys.server_principals AS sp2 ON sp2.principal_id = spe.grantee_principal_id
WHERE (sp2.name=N'public')
GO


--	查看当前数据库的角色 public 及用户 guest 的权限
SELECT role_name = u.name,p.state_desc,p.permission_name,[schema] = SCHEMA_NAME(o.schema_id),object_name = o.name,o.type 
FROM sys.database_permissions p
INNER JOIN sys.database_principals u ON p.grantee_principal_id = u.principal_id
INNER JOIN sys.all_objects o ON o.object_id = p.major_id
WHERE p.grantee_principal_id IN (0,2) --(0:public;2:guest)
ORDER BY u.name,o.schema_id,o.name,p.permission_name;
GO


--	回收所有数据库角色public的权限
DECLARE @database	varchar(100),@permission varchar(100),@object		varchar(100),@role		varchar(100),@schema		varchar(100),@sql		nvarchar(1000)

DECLARE CSR_DB CURSOR FAST_FORWARD FOR 
	SELECT name FROM sys.databases ORDER BY name;
	
OPEN CSR_DB;
FETCH NEXT FROM CSR_DB INTO @database;

WHILE (@@fetch_status = 0)
BEGIN
	SET @sql = 
		'DECLARE CSR_Objects CURSOR FAST_FORWARD FOR 
		SELECT p.permission_name,role_name = u.name
		FROM [' + @database + '].sys.database_permissions p
		INNER JOIN [' + @database + '].sys.database_principals u ON p.grantee_principal_id = u.principal_id
		INNER JOIN [' + @database + '].sys.all_objects o ON o.object_id = p.major_id
		WHERE p.grantee_principal_id IN (0,2) 
		ORDER BY u.name,p.permission_name;';
	EXECUTE sp_executesql @sql;

	OPEN CSR_Objects;
	FETCH NEXT FROM CSR_Objects INTO @permission,@schema,@object,@role;
	
	WHILE (@@fetch_status = 0)
	BEGIN
		SELECT @sql = 'USE [' + @database + ']; REVOKE ' + @permission + ' ON [' + @schema + '].[' + @object + '] FROM [' + @role + '];';
		--EXEC sp_executesql @sql;
		PRINT @sql
		FETCH NEXT FROM CSR_Objects INTO @permission,@role;
	END
	
	IF @database NOT IN ('master','tempdb')
	BEGIN
		SELECT @sql = 'USE [' + @database + ']; REVOKE CONNECT FROM GUEST;';
		--EXEC sp_executesql @sql;
		PRINT @sql
	END
	
	CLOSE CSR_Objects;
	DEALLOCATE CSR_Objects;

	FETCH NEXT FROM CSR_DB INTO @database;
END
CLOSE CSR_DB;
DEALLOCATE CSR_DB;
GO

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!