如何更改the中登录的默认数据库

Question

我试图将sql server实例中登录的默认数据库更改为映射到的数据库。当前，主数据库中所有登录的默认数据库。

Alter login login-name with default_database = --'NEED HELP HERE to put the logic'

谢谢

Answer 1

此查询将帮助您将任何默认数据库设置为所有/指定的登录名，并将其映射到。

DROP TABLE IF EXISTS #temp
CREATE TABLE #temp
(
[Login] sysname,
DBName varchar(512)
)

insert into #temp
exec sp_msforeachdb 'USE ? select name [login],db_name() from sys.database_principals where type=''U'' and [name] not like ''##%'''
-- Here you can add "where [name]= ''Login_name'' " if you want to set default database for specific login
DECLARE @QUERY NVARCHAR(1024)
DECLARE My_Cursor CURSOR FOR
    select 'ALTER LOGIN '+[Login]+' WITH DEFAULT_DATABASE=['+DBName+']'
    from
    (
    select
            distinct row_number() over(partition by t.[Login] order by t.DBName) Id,[Login],t.DBName
    from    #temp t inner join syslogins s on t.[Login]=s.[name]
    ) a

where Id=1

OPEN My_Cursor
FETCH NEXT FROM My_Cursor INTO @QUERY
WHILE @@fetch_status=0
    BEGIN
    EXEC (@QUERY);
    FETCH NEXT FROM My_Cursor INTO @QUERY
    END
CLOSE My_Cursor
DEALLOCATE My_Cursor