主页 / dba / 问题 / 12817
Accepted
Daniel James Bryars
Asked: 2012-02-14 07:31:35 +0800 CST

在 SQL Server 2008 R2 数据库中是否有“Auto_Fix”所有孤立用户的速记方法？

  • 772

使用以下方法将单个孤立的 SQL 用户修复为登录相当简单：

EXEC sp_change_users_login 'Auto_Fix', 'user'

我可以编写脚本，但是是否有一个现有的存储过程会自动尝试修复给定数据库中的每个孤立用户？

sql-server-2008-r2

6 个回答

  1. Best Answer

    Ted Krueger（推特上的@onpnt）写了一个很棒的脚本来做到这一点。它为没有登录名的任何用户添加登录名并运行 auto_fix。他甚至写了一篇文章，其中包括修复 Windows 登录：

    http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/fixing-orphaned-database-users

    当然，如果您想先对其进行测试（或仅执行审计），您可以注释掉操作行 ( EXEC)，然后打印出结果。

    • 16

  2. 基于上述脚本，我们可以像这样使用 sp_MSForeachdb 修复实例中的所有用户

    declare @name varchar(150)
declare @query nvarchar (500)

DECLARE cur CURSOR FOR
    select name from master..syslogins

Open cur

FETCH NEXT FROM cur into @name

WHILE @@FETCH_STATUS = 0
BEGIN

set @query='USE [?]
IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''
BEGIN   
exec sp_change_users_login ''Auto_Fix'', '''+ @name +'''
END'

EXEC master..sp_MSForeachdb @query

    FETCH NEXT FROM cur into @name

END

CLOSE cur
DEALLOCATE cur

    希望它会有所帮助

    • 6

  3. 下面是完美完成这项工作的简单脚本——

    USE DBNAME     ----- change db name for which you waant to fix orphan users issue

GO


declare @name varchar(150)

DECLARE cur CURSOR FOR
    select name from master..syslogins

Open cur

FETCH NEXT FROM cur into @name

WHILE @@FETCH_STATUS = 0
BEGIN

    EXEC sp_change_users_login 'AUTO_FIX', @name

    FETCH NEXT FROM cur into @name

END

CLOSE cur
DEALLOCATE cur
    • 2

  4. 这将是使用 dbatools 命令Repair-DbaDbOrphanUser的一个很好的用例

    首先，您可以识别孤立用户

    Get-DbaDbOrphanUser -SqlInstance $sqlinstance

ComputerName : SQL01
InstanceName : MSSQLSERVER
SqlInstance  : SQL01
DatabaseName : SockFactoryApp
User         : SockFactoryApp_User

    然后解决它们

    Repair-DbaDbOrphanUser -SqlInstance $sqlinstance

ComputerName : SQL01
InstanceName : MSSQLSERVER
SqlInstance  : SQL01
DatabaseName : SockFactoryApp
User         : SockFactoryApp_User
Status       : Success
    • 1

  5. 为所有用户修复单个数据库的孤立用户

    BEGIN 

DECLARE @username varchar(25) 

DECLARE fixusers CURSOR 
FOR 

SELECT UserName = name FROM sysusers 
WHERE issqluser = 1 and (sid is not null and sid <> 0x0) 
and suser_sname(sid) is null 
ORDER BY name 

OPEN fixusers 

FETCH NEXT FROM fixusers 
INTO @username 

WHILE @@FETCH_STATUS = 0 
BEGIN 
EXEC sp_change_users_login 'update_one', @username, @username 
FETCH NEXT FROM fixusers 
INTO @username 
END 


CLOSE fixusers 

DEALLOCATE fixusers 

END 

go
    • 0 
  6. EXEC sp_MSforeachdb 'USE ? exec sp_change_users_login ''Auto_Fix'', ''Username'';'
    • -2

相关问题