How can I update the roles for all logins using T-SQL?

Question

I need to update the roles for all database logins, but without using SQL Server Management Studio. I can update a single user with the following example SQL.

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

But how I can do this for all users? Is there a way to select the names of all the users and then loop through the list and update them all?

Answer 1

You can use dynamic 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 !!

Answer 2

You can use this to build the script that you can then view and run separately.

select 
'ALTER SERVER ROLE [dbcreator] ADD MEMBER [' + sl.loginname+ ']
GO
' as SQLCommand
from sys.syslogins sl

You can also modify the query to filter on database name sl.dbname or username `sl.loginname'

Execute each row in the output to add the users to the role.