Nicholas Aysen Nicholas Aysen - 1 year ago 72
SQL Question

How to add users to a SQL database in a C# Winform

I am trying to insert users into a SQL database, but not the way you are thinking.
Usually, in SQL Management Studio, one would enter new users through the Security - Logins wizard. But How can one do this in a C# Winform?

Basically, my Winform would have various fields to capture, but the important ones are the Username field and all the selected Roles
enter image description here

In this form, once a users clicks Add, the data will be stored into a separate SQL table. I would like to add the user to the database credentials

enter image description here
enter image description here

Once they have been added to the security credentials, they have the default data:

  • Windows user/s

  • Default schema of [dbo]

  • If possible, a server role of public

  • The user mapping is the main area to focus on here. For each role that the user must have, that must be assigned to them for that database. So if a user's roles are Capturer and Admin, then they will have those role memberships mapped to that database for the newly created user. The roles that are selected do exist in the db, so there shouldn't be any errors here.

enter image description here

Is this possible to do via C# Winforms? Would this be possible using a dataset? Would this be conventional C# code ie.

SqlConnection //the rest of the code follows here

Or would there have to be a new interface/form to utilise?

Answer Source

If you use SSMS, and right click on any object (including logins, users, etc), you can choose options to see the generation script:

enter image description here

enter image description here

This will show you what commands you need to execute to create that object. Remember that you need both the login (to the server) and user (to the database) entries. So: just issue equivalent commands! For some things like passwords, you many need to consult the CREATE LOGIN etc documentation, since the server is unable to script those out. Note that this does mean your application needs to run with enough access to manage users.