T-Sql

如何使用 T-SQL 更新所有登錄的角色?

  • August 21, 2019

我需要更新所有數據庫登錄的角色,但不使用 SQL Server Management Studio。我可以使用以下範例 SQL 更新單個使用者。

EXECUTE sys.sp_addsrvrolemember
   @loginame = N'User1',
   @rolename = N'dbcreator';

但是我怎樣才能為所有使用者做到這一點呢?有沒有辦法選擇所有使用者的姓名,然後遍歷列表並全部更新?

您可以使用動態 sql。

declare @sqltext nvarchar(max) = N''

-- here you are adding all the logins to dbcreator fixed server role ...
-- this is just an example to get you started ...adjust it as per your needs !!
select @sqltext += 'ALTER SERVER ROLE dbcreator ADD MEMBER '+QUOTENAME(name)+';'+char(10)+'go'+char(10) from sys.server_principals 
where type_desc ='SQL_LOGIN' -- only for sql logins 
and name <> 'sa' -- not sa
and name not like '##%' -- not special logins like certs etc 
and is_disabled = 0 -- not disabled 

print @sqltext 
--once you are fine with the output then uncomment below 
-- exec sp_executesql @sqltext -- this will actually run the sql !!

引用自:https://dba.stackexchange.com/questions/245871