T-Sql
如何使用 T-SQL 更新所有登錄的角色?
我需要更新所有數據庫登錄的角色,但不使用 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 !!