0

I simply want to add multiple users that I have created to a created role then grant all privileges on said role. This is what I have thus far:

    CREATE USER Bob FROM LOGIN Bob;
    CREATE USER Billy FROM LOGIN Billy;
    CREATE USER Bobby FROM LOGIN Bobby;
    CREATE ROLE Users;
    ALTER ROLE Users ADD MEMBER Bob;
    GRANT ALL PRIVILEGES ON ABCdatabase.* TO Users;

I want to add Billy and Bobby, is there a way to add them to the ALTER line or do I have a create a statement for each members? Any improvements to the code above is helpful.

What kind of SQL server am I using? I am not necessarily using any specific type. I merely need to provide code for a school assignment and am not testing any of this on any particular system. Thanks in advance.

0

2 Answers 2

1

For SQL Server: As far as I know ALTER ROLE will add a single member per statement.

If you use the GUI to add [Bob], [Billy] and [Bobby] to the role [Users], you'll see that SQL generates one line for each user and executes them sequentially (ignoring database name):

USE [database]
GO
ALTER ROLE [Users] ADD MEMBER [Bob]
GO
USE [database]
GO
ALTER ROLE [Users] ADD MEMBER [Billy]
GO
USE [database]
GO
ALTER ROLE [Users] ADD MEMBER [Bobby]
GO

Refer to https://msdn.microsoft.com/en-us/library/ms189775.aspx

0

In SQL server you can only add a single user per ALTER ROLE statement, so your script is about as good as it can get (aside from adding Billy and Bobby to the role).

https://msdn.microsoft.com/en-us/library/ms189775.aspx

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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