change sp_addrolemember for ALTER ROLE

[leedavi]

The sp_addrolemember in SQL will be removed in future. You probably know!

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addrolemember-transact-sql?view=sql-server-ver16

I was automating some work and took some of your DB permissions code to do it. The sp_addrolemember gave me some issues with roles already being assigned. I changed it to the ALTER ROLE and the problem was solved.

Change

SqlCommand addRoleMember = new SqlCommand($"sp_addrolemember 'db_owner', '{appPoolName}'", myConn);

to

SqlCommand addRoleMember = new SqlCommand($"ALTER ROLE [db_owner] ADD MEMBER [{appPoolName}] ", myConn);

[david-poindexter]

Nice @leedavi - thanks! 🎉

[david-poindexter]

Note to future self:
Here is the line of code being referenced - https://github.com/nvisionative/nvQuickSite/blob/develop/nvQuickSite/Controllers/DatabaseController.cs#L211