从SQL Server 2012中删除用户时出错

qltillow  于 2022-11-21  发布在  SQL Server
关注(0)|答案(3)|浏览(373)

我尝试从SQL Server中删除除默认内置SQL Server登录帐户之外的所有登录帐户,但无法删除**\administrator**帐户。这会导致以下错误:
服务器主体“\administrator”已授予一个或多个权限。请在删除服务器主体之前撤消这些权限。
我尝试使用以下查询检查分配给此用户的权限:

Select * 
  from sys.server_permissions 
  where grantor_principal_id = 
           (Select principal_id 
              from sys.server_principals 
              where name = N'<domain>\administrator')

此查询仅返回一条与端点对应的记录,如下所示:

class   class_desc  major_id    minor_id    grantee_principal_id    grantor_principal_id    type    permission_name state   state_desc

105 ENDPOINT    65536   0   269 259 CO      CONNECT G   GRANT

但是,当我尝试在所有现有端点上检查分配给此用户的权限时,我发现没有一个端点对我尝试删除的用户具有任何类型的权限。
我不确定发生了什么情况,也不知道要在哪里删除此用户。

xuo3flqw

xuo3flqw1#

我解决了这个问题。以下问题不允许我从SQL Server删除**\administrator登录名:
1.ReportServer
ReportServerDB数据库的所有者是\管理员用户
1.ConfigMgrEndPoint端点的拥有者也是
\administrator使用者。
我更改了上述所有SQL对象的所有权。我将
sa用户设置为它们的新所有者。然后,我成功删除了\administrator**用户。我还从帮助我解决此问题的一位同事那里得到了以下Maven意见:
将[sa]保留为大多数SQL对象的默认所有者是一种标准做法。如果某个域用户不再存在或在Active Directory中被禁用,则将该用户设置为SQL对象的所有者可能会影响以后的工作

93ze6v8z

93ze6v8z2#

为了找出阻止删除登录的权限,我使用了this脚本:

SELECT @@SERVERNAME,@@SERVICENAME
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @GrantorName nvarchar(4000)

SET @GrantorName = 'xxx\the_login'  /* Login in Question */

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 = @GrantorName
)

有时是this一:

--Check to see if they own the endpoint itself:
SELECT SUSER_NAME(principal_id) AS endpoint_owner ,name AS endpoint_name
FROM sys.database_mirroring_endpoints;

--If so, you'll need to change the endpoint owner. Say the endpoint is called Mirroring, and you want to change the owner to SA:
--ALTER AUTHORIZATION ON ENDPOINT::Mirroring TO sa;

或遵循these说明:

--1)  Check to see if this logon only has server level permissions and check to see 
--if this login has granted permissions to another server principal. 
--Use this query to identify the permissions granted.

Select perm.* from sys.server_permissions  perm
INNER JOIN sys.server_principals prin ON perm.grantor_principal_id = prin.principal_id
where prin.name = 'xxx\the_login'   /* Login in Question */

--2) The permissions granted will need to be revoked , to allow the DROP LOGIN to complete. 
--The permissions can be granted again by a suitable LOGIN.

还有一篇很好的文章与此相关:
Drop Login issues for logins tied to SQL Server Availability Groups

yqhsw0fo

yqhsw0fo3#

您必须检查“服务器权限”和“显式权限”。

相关问题