Hi Tom, For roles and permissions, they should just go directly in the DB projects. Nothing special about them.
Users/logins are different. Ideally, you’d have AD or AAD based groups that are added to the roles, and again they can go right in the project. If you’re looking after the DB or deploying it, you really shouldn’t be getting involved in who is in those groups, and there’s nothing more for you to do. If you have to use SQL logins/users (and you really should now be trying to avoid those), you can put them in the projects but you don’t want passwords there. It then depends how you are deploying the projects. If it’s something like Azure Pipelines or GitHub Actions, then you should put the credentials that must be there into Azure Key Vault or similar, and retrieve them from there during deployment. If you are in an environment like Azure, then you should be reassessing why you need the SQL logins. If it’s an app connecting to a DB, in many cases, a user-assigned managed identity would be a better option. Regards, Greg Dr Greg Low 1300SQLSQL (1300 775 775) office | +61 419201410 mobile SQL Down Under | Web: https://sqldownunder.com<https://sqldownunder.com/> | About Greg: https://about.me/greg.low From: Tom P via ozdotnet <ozdotnet@ozdotnet.com> Sent: Thursday, 8 December 2022 9:58 AM To: ozDotNet <ozdotnet@ozdotnet.com> Cc: Tom P <tompbi...@gmail.com> Subject: [OT] Creating logins/users/roles/permissions in Visual Studio sqlproj projects Hello I'm fairly new to Visual Studio sqlproj projects and was wondering what the normal practice is for managing database security scripts (user logins, users, roles, permissions). I have seen some other projects here where the previous developers had the create login SQL code for example in post-deployment scripts, however, I see this being a problem since different environments (different publish profiles in the project) would require different logins and different security altogether. On top of that I'm not even sure how the passwords would be managed in this case as they would need to be hardcoded in the scripts. In the past I'm sure I've only ever seen the security purely managed by the DBAs in external scripts (or manually) and not in the Visual Studio project itself. Any thoughts or recommendations would be much appreciated. Regards Tom
