主页 / dba / 问题 / 19456
Accepted
rsteckly
Asked: 2012-06-19 09:54:54 +0800 CST

数据库主体在数据库中拥有一个架构，并且无法删除消息

  • 772

我试图从数据库中删除一个主体，但不能因为它拥有一个模式。但是，当我去编辑用户时，取消选中模式的框是蓝色的且不可移除。如何从这些模式中删除主体？

sql-server sql-server-2008

3 个回答

  1. Best Answer

    尝试使用 T-SQL 来执行此操作：

    alter authorization
on schema::YourSchemaName
to dbo
go

drop user TheUserYouWantToDelete
go

    您不能删除作为架构所有者的主体，因此将ALTER AUTHORZATION拥有的架构（我使用过YourSchemaName，但显然将其替换为数据库中的拥有架构）更改为dbo（同样，您可以将所有权更改为您需要的任何主体环境）。这将允许您删除以前拥有模式的用户（例如我使用的目的TheUserYouWantToDelete，但这将是您想要删除的现在非所有者）。

    • 43

  2. T-SQL 方式有效。感谢 http://zarez.net/?p=179 我找到了 SSMS 方法来做到这一点（UI）。

    要从 Sql Server Management Studio 更改架构所有者：

    Expand your database -> Security -> Schemas

    在 Object Explorer Details 中，您可以看到架构和所有者的列表：

    在此处输入图像描述

    如果您不知道用户拥有什么架构，请检查用户的属性。

    在此处输入图像描述

    打开用户拥有的模式的属性，然后单击“搜索”以查找新所有者。如果您不认识新的所有者，您可以“浏览”一个。

    Properites -> Search -> Browse

    并且您可以将架构所有者更改为 dbo （或最合适的人）。

    在此处输入图像描述

    • 41

  3. 之前已经表明，在删除用户之前，您需要dbo根据以下示例重新分配它可能拥有的任何模式：

    alter authorization
on schema::YourSchemaName_Ownedby_TheUserYouWantToDelete
to dbo
go

drop user TheUserYouWantToDelete
go

    但是，有时您要删除的用户分配有多个模式。您可以使用以下脚本找到特定用户拥有的所有模式：

      SELECT S.* 
       ,[the schema ownwer]=dp.name
       ,dp.type_desc
FROM SYS.schemas S
INNER JOIN SYS.database_principals dp
       ON S.principal_id = dp.principal_id

    在此处输入图像描述

    请注意：在上面的示例中，用户mathura\radhe有几个模式，包括db_owner虽然这个用户不是这个数据库的数据库所有者。

    下面是我用来准备在数据库中删除用户的脚本的部分视图。

    还有其他事情需要考虑 - 因为它们不包含在此脚本中：

    1. 在删除之前获取该用户的所有权限 - 以防万一你需要回滚 - 为此我推荐这个或这个
    2. 我没有检查分配给该用户的角色 - 只是因为到目前为止我从未遇到过这个问题
    3. 我不是在编写登录和用户创建脚本——在你放弃它之前你可能需要这个

    4. 在运行此脚本之前，请始终检查您是否在正确的服务器和正确的数据库中

        SELECT   THE_SERVER      = @@SERVERNAME
      ,THE_DATABASE    = QUOTENAME(DB_NAME())     
      ,THE_USER        = QUOTENAME(dp.name)
      ,IS_ORPHAN       = dp.IS_ORPHAN
      ,[DROP_USER]     = 
       'USE ' + QUOTENAME(DB_NAME()) + 
       '; DROP USER [' + dp.name           
        + '] '      
      ,[RESET_SCHEMA]  = 
      'USE ' + QUOTENAME(DB_NAME()) + 
      ';' + dp.THE_SCHEMAS  +  
      ';'       
      ,[RESET_ROLE]    = 
      'USE ' + QUOTENAME(DB_NAME()) + 
      '; ALTER AUTHORIZATION ON Role::[' + dp.name           
      + ']  TO [dbo]'

FROM
  (SELECT
  [NAME]               = dp.name
 ,IS_ORPHAN            = CASE WHEN (sp.sid is null  
                         AND c.sid is null   
                         AND a.sid is null) 
                         THEN 1 ELSE 0 END
 ,THE_SCHEMAS          = STUFF((   
             SELECT N';'+ N'ALTER AUTHORIZATION ON SCHEMA::' + 
             CAST (QUOTENAME(s.name) as sysname) + SPACE(1) + 'TO [dbo]'
                                    FROM sys.schemas s
                                    WHERE s.principal_id = dp.principal_id
                                    FOR XML PATH ('')),1,1,'')

FROM sys.database_principals dp
 LEFT OUTER JOIN sys.server_principals sp
              ON dp.sid = sp.sid
 LEFT OUTER JOIN sys.certificates c
              ON dp.sid = c.sid
 LEFT OUTER JOIN sys.asymmetric_keys a
              ON dp.sid = a.sid

WHERE 1=1
 AND dp.type in ('U', 'S', 'C', 'K') 
 AND dp.principal_id > 4 
        /*0..4 are system users which will be ignored*/
 AND NOT (dp.type = 'S' 
         AND LEN(dp.sid) = 28) 
        /*to filter out the valid db users without login*/) dp    
 WHERE dp.name = 'mathura\radhe'

    在此处输入图像描述

    然后我复制在 RESET_SCHEMA 列上生成的脚本：

    USE [sgdragnet];
ALTER AUTHORIZATION ON SCHEMA::[mathura\radhe] TO [dbo];
ALTER AUTHORIZATION ON SCHEMA::[radhe] TO [dbo];
ALTER AUTHORIZATION ON SCHEMA::[mathura] TO [dbo];
ALTER AUTHORIZATION ON SCHEMA::[KRISHNA] TO [dbo];
ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [dbo];
ALTER AUTHORIZATION ON SCHEMA::[db_securityadmin] TO [dbo];

    其他列也包含重要信息。希望这可以帮助

    • 1

相关问题