CODEWITHSUNDEEP
c#sql-server
Goran
Goran

Reputation: 6518

Confused with user management in SQL Server

I have created a WPF application that connects to a SQL Server database directly (not through services), located on database server. Now I need to create user management, where each user will have its own log in credentials (not windows authentication). This is how I thought of doing it:

  1. During the application installation, DB administrator will provide me with an account that contains enough permissions to create database

  2. Using these credentials I will connect to SQL Server, execute a script to create database, and also execute a script to create one default admin user for this database, that will have full permissions.

  3. I will use this new user to manage all users and roles for my database.

  4. for each user in my application I need to create a user in my database.

Questions:

Or I am missing something?

Upvotes: 1

Views: 321

Answers (2)

Yusubov
Yusubov

Reputation: 5843

I would probably do it differently by creating roles and managing things through roles rather than user names/logins. It would be better to create one application login in Database, whose credentials will be used for communication between your DB and application.

Upvotes: 1

Peter
Peter

Reputation: 9712

There is only very minor overlap. In your Users table you store the username that matches up with the SQL account, the username is the only overlap. All other information is stored in the Users table.

I don't see any problem with doing it this way, the only downside is that you are required to have an admin account with a considerable amount of access on whatever SQL Server it's installed on, but it sounds like you have already taken that into consideration.

Upvotes: 0

Related Questions