login/account/users in sql server

Question

Hello All;

Please see my requirement below

I would like to create a new database. I would like the database to be accessed by several users. Based on the listed documentation below, it stated and I quote from the documentation.

https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/getting-started-with-database-engine-permissions?view=sql-server-ver15

"Logins are granted access to a database by creating a database user in a database and mapping that database user to login. Typically the database user name is the same as the login name, though it does not have to be the same."

which means I could create a database user with the same windows login name and assign permissions to the database user. Hence, does that mean if I am working on creating an asp.net application and i want the application to have access to that database, I should create a database user for that application for instance called testwbuser and assign specific permission to testwbuser . Then use that testwbuser and its credential within my asp.net application to access the database.

Thanks, all help is appreciated

Answer 1

Hi @oly ,

"Logins are granted access to a database by creating a database user in a database and mapping that database user to login. Typically the database user name is the >same as the login name, though it does not have to be the same."
which means I could create a database user with the same windows login name and assign permissions to the database user. Hence, does that mean if I am working on >creating an asp.net application and i want the application to have access to that database, I should create a database user for that application for instance called >testwbuser and assign specific permission to testwbuser . Then use that testwbuser and its credential within my asp.net application to access the database.

Yes. You are right.
Login: The login name is based on the instance level, not a database level
User: Username is based on a database
Relationship: They are one to one relationship

What you need to do as next, just change YX\Client to your own name, and change testdb to your db name, I use windows Authentication mode on my side:

USE testdb  
GO  
--create login from windows, if you have already did this,skip this step  
CREATE LOGIN [YX\Client] FROM WINDOWS   
GO  
  
  
USE testdb  
GO  
--create user for this login, if you have already did this, skip this step  
CREATE USER [YX\Client] FOR LOGIN [YX\Client]  
GO  
  
USE testdb  
GO  
--grant read permission for this login of this testdb db,you can grant it other suitable permissions on your side  
ALTER ROLE [db_datareader] ADD MEMBER [YX\Client]  
GO  
  
--check the perimissions of users in testdb db  
USE testdb;  
WITH CTE AS  
(  
SELECT u.name AS UserName,  
g.name AS dbRole,  
'√' as 'flag'  
FROM sys.database_principals u  
INNER JOIN sys.database_role_members m ON u.principal_id = m.member_principal_id  
INNER JOIN sys.database_principals g ON g.principal_id = m.role_principal_id  
)  
SELECT * FROM CTE PIVOT(MAX(flag) FOR dbRole IN ([public],  
[db_owner],  
[db_accessadmin],  
[db_securityadmin],  
[db_ddladmin],  
[db_backupoperator],  
[db_datareader],  
[db_datawriter],  
[db_denydatareader],  
[db_denydatawriter])) as rg;  

More information: how-to-connect-sql-database-in-asp-net-using-c-sharp-and-insert-and-view-the-data-usi

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

I should create a database user for that application for instance called testwbuser and assign specific permission to testwbuser

If there is no already existing user and you want a exclusive user for the application then yes you need to have a user created. Now only creating user and not mapping it with login will lead to orphaned user. So you need to create a login first, then user and then map the user with this login. Now give whatever permission you want to this user on database.

Answer 3

Hence, does that mean if I am working on creating an asp.net application and i want the application to have access to that database, I should create a database user for that application for instance called testwbuser and assign specific permission to testwbuser .

Maybe. Maybe not.

You have the choice: it is common to use an application login which the application picks up from a config file or whatever, and then the application knows who is the real user by other means.

But it does not have to be that way. The application can also log in with Integrated Security and impersonate the actual users. How to do that is not a question for an SQL Server forum, better to ask ASP .NET people. I don't know what they think about it, but personally I prefer this setup. (But I don't know how well it works when web server and SQL Server are on different machines.)

Answer 4

Hi @oly ,

Is the reply helpful?

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 5

Yes it was very helpful. Thanks