主页 / dba / 问题 / 90135
Accepted
R1cky
Asked: 2015-01-26 14:58:53 +0800 CST

如何将默认数据库更改为所有登录名？

  • 772

我对 SQL Server 有点陌生，我正在尝试编写 Microsoft SQL Server (2005-2014) 安装后活动的脚本，但我在更改登录名方面有点卡住了。公司政策规定没有登录名应将 master 数据库作为默认值，因此我的计划是识别所有将 master 作为默认值的登录名并将 master 更改为 tempdb。

可以使用这样的查询更改单一登录：

ALTER LOGIN [sa] WITH DEFAULT_DATABASE = tempdb

试图在所有登录名上实现这一点（不知道他们的数量或名称），我知道我很天真，希望这样的事情会起作用（但必须尝试）：

UPDATE master..syslogins
SET dbname = 'tempdb'
WHERE dbname = 'master'

显然输出是一个错误（逻辑上）：

Ad hoc updates to system catalogs are not allowed.

SQL Server 处于 MIX 身份验证模式，所以几乎任何东西都可以登录 - SQL 登录、Windows 本地或域用户或组（不确定这是否会对解决方案产生任何影响）。解决方案应该几乎是版本独立的，但是假设从 SQL Server 2005 开始（......是的，仍然有一些古老的版本......）

谁能帮我找到适当的查询来实现这一目标？

sql-server t-sql

2 个回答

  1. Best Answer

    您永远不应该尝试直接更新系统表，在大多数情况下它不会像您发现的那样让您这样做。

    ALTER LOGIN在您的情况下，您将希望在不知道实例上有多少的情况下构建动态语句。但是，您在执行此操作时需要小心谨慎，以确保您不触碰您不应该触碰的登录信息。此查询应该为您提供那些“非默认”或通过安装过程安装的登录：

    SELECT name
FROM sys.server_principals
WHERE type <> 'R'
    AND name NOT LIKE '##%' --Exclude policy based accounts
    AND name NOT LIKE 'NT%' --Exclude system accounts
    AND sid <> 0x01 --exclude the sa

    从这里你只需要构建你的语句来执行 via sp_executesql。您可以根据自己的喜好使用简单的光标或 while 循环，下面是我方便使用的基本光标的模板：

    DECLARE @v1 VARCHAR(50)

DECLARE v_cursor CURSOR FOR  
/* Query of data to iterate through */ 

OPEN v_cursor  
FETCH NEXT FROM v_cursor INTO @v1   

WHILE @@FETCH_STATUS = 0   
BEGIN   
    /* build your dynamic statement */
    /* Use @v1 within query to perform action on each row */
    FETCH NEXT FROM v_cursor INTO @v1   
END   

CLOSE v_cursor   
DEALLOCATE v_cursor;
    • 5

  2. 下面是生成所需脚本的示例。

    DECLARE @SqlScript nvarchar(MAX);
SELECT @SqlScript = (SELECT N'ALTER LOGIN ' + QUOTENAME(name) + N' WITH DEFAULT_DATABASE = tempdb;'
FROM sys.server_principals
WHERE 
    default_database_name = 'master'
    AND type NOT IN('C', 'K')
FOR XML PATH(''));

EXEC sp_executesql @SqlScript;
    • 1

相关问题