CODEWITHSUNDEEP
sqlsql-server
amateur
amateur

Reputation: 44625

grant permissions to multiple database users in one line

I have the following line in my stored procedures to grant permissions to a user for this stored procedure

GRANT EXECUTE ON [dbo].[GetItem] TO [<<DBUSER>>]

The stored procedure is consolidated in to one big script with others using Powershell where I replace the <> value with a user name.

I would like to modify this so that I can grant permissions to an user in a list that exists in that database. For example:

GRANT EXECUTE ON [dbo].[GetItem] TO [User1, User2, User3]

Is it possible with SQL, to do this? I am trying to minimize the changes as much as possible. If the user does not exist in the database, it just ignores it.

Upvotes: 0

Views: 4096

Answers (1)

Pondlife
Pondlife

Reputation: 16240

According to the documentation for GRANT, you can indeed do this. But because of the brackets, your query is trying to grant permission to one user called [User1, User2, User3]. You probably want this:

GRANT EXECUTE ON [dbo].[GetItem] TO [User1], [User2], [User3]

Although as Gordon pointed out, a group would be much easier in this case if possible.

Upvotes: 2

Related Questions