How to allow a certain domain user to create databases on a remote SQL instance? I want to allow for a certain domain user to be able to create databases on a remote SQL instance.
What steps are necessary and is it possible to perform those steps programmatically in a C# application ?
Should I first create a login if doesn't exist and then give it the dbcreator server role?
Related question: Safe and secure implementation of logon trigger in SQL Server 2014 Express edition?
Yes you would need a Login for that domain user to exist first. If all they need is the ability to manage databases (create, alter, drop, restore them) then the server role dbcreator should be sufficient.
If you anticipate multiple domain users needing access to this, then you should instead create a Login for a correlating Active Directory Group, so you don't have to continually manage setting up multiple Logins. You will then be able to map that AD Group to the server role dbcreator. (You can even use the built in &quot;Domain Users&quot; group if you wanted everyone on the domain to have access to creating databases.)
As far as how to accomplish this in C#, there's a multitude of ways, and that's perhaps a better question for StackOverflow because it's dependent on how you're accessing and querying your server instance. For example, with ADO.NET you could simply write a SQL query to check if a Login exists for the current user, if not create it, and assign the role. Or you can even create a procedure that takes in the current user of the application, and handles that logic for you (which will be more consumable depending on what database access framework you're using). But you'll likely have the easiest time managing this by creating an appropriate AD Group and mapping it to a Login with the dbowner role once.