Issue in setting multiple DB roles for one SQL login with MSFT_xSQLServerDatabaseRole

[sunbinzhu]

I met error when I use MSFT_xSQLServerDatabaseRole to grant db_owner role to one SQL login for multiple databases. For example, i want to grant UserA the db_owner role for DB1, DB2, DB3, i created 3 configuration items, with 'Name' as UserA, and 'Database' as DB1, DB2, DB3 separately. It failed to deploy because 'Name' is the only Key parameter for MSFT_xSQLServerDatabaseRole. And i had specified 3 items with same key but different values. Promoting parameter 'Database' as the second Key can fix this error.
My question is whether what i am doing is the correct way to grant multiple DB roles for one SQL login? If so I think you shall promote Database as Key as well. If not, please advise what is the correct way. Thanks.

[johlju]

You are correct. This seems to be a bug. Right now it only supports unique logins regardless of database.
DatabaseName is a [string] so it handles only one database per login as well. So a code fix need to be done to handle this.

[johlju]

This should be able to handle different instances on the same server as well

[sunbinzhu]

And maybe make 'Role' as an array to grant multiple roles for one login?

[johlju]

That too. That would be a nice enhancement as well.

[johlju]

@sunbinzhu and @typozh I have fixed this issue, but before I can PR I need to write a test for this resource. I will PR this weekend. But if you want to try it out you can find the fixed resource in my working branch in my fork of xSQLServer

[johlju]

@kwirkykat Can you add breaking change tag to this issue?

[johlju]

This issue was resolved in merged PR #115. Let us know if there is any further problems.