0

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?

3
  • what version of sql server are you using ?
    – Kin Shah
    Aug 21, 2019 at 13:32
  • @KinShah SQL Server 2016 Express. I'm using OSQL to run the query.
    – Ben
    Aug 21, 2019 at 13:33
  • At database level, you can play with sys.sysusers table: SELECT name, 'ALTER ROLE dbcreator ADD MEMBER ' + name AS script FROM sys.sysusers WHERE issqluser=1 AND uid > 4
    – Dat Nguyen
    Aug 21, 2019 at 13:59

2 Answers 2

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 !!
2
  • You'll have to copy and paste that script and run it, as it's a multi-batch script with go's Aug 21, 2019 at 13:55
  • Or you can just remove the batch separator GO .
    – Kin Shah
    Aug 21, 2019 at 15:36
0

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.

2
  • why use sys.syslogins deprecated view from sql server 2000 ?
    – Kin Shah
    Aug 21, 2019 at 15:37
  • I didn't realise it was deprecated and it still works on my Azure Managed Instance 👍
    – KevH
    Aug 22, 2019 at 9:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.