Sql Database is not accessible from another User Account

Question

Hello all Client PC is having 2 user accounts in his system i.e Admin1 Admin2. As SQL Server database is installed on the Local machine there not might be any issue with the user accounts My program creates the database on one of the user to work with my program now client switches to another user account (i.e Admin2) and my database is not accessible. I tested it on my PC also it was not allowed niether with my program nor with SSMS. How to resolve this issue so that database can be accessible for all the user.

Tapan Patel · Answer

what you are trying to do is of broad topic (in terms of design and development). I would suggest to go through this link and understand the same. https://stackoverflow.com/questions/5029014/how-to-add-active-directory-user-group-as-login-in-sql-server

Devendra Kumar · Answer

Can you share the steps to do this as I am not so much familiar for adding a database user. Although I tried something like you said but the database is not listing there also it might be possible of my mistakes. Please share Edit: Isn't there any way while creating db I should add such thing so that my db can be accessible to all user

Tapan Patel · Answer

Okay so it seems that as you created DB using admin1 (and you use win auth) to login to db it works fine. To resolve this issue When you are switching the admin2 somehow you need to add that user to that db and then admin2 would work fine. Till then admin2 will not be able to use it.

Devendra Kumar · Answer

@Tapan Sir Here the user account is not the database user its a Windows user. The thing is I created a database from the admin1 (window user) as I login to sql server as .\SqlExpress where we know dot indicates the current user. Now client switches to another user admin2 (window user) he cannot access that database whereas system database such as master is accessable. How can I solve this?

Tapan Patel · Answer

Two things to consider: 1. When you say client switches to Admin2 does this user already exist in database (meaning does this user have an access to that DB)? 2. When client switches to Admin2 ( considering that #1 covered or Admin2 already exists in db) are you using the credentials same as used for Admin1 to login to DB?

Devendra Kumar · Answer

Isn't there any solution?

Devendra Kumar · Answer

Thanks shakti for your valuable reply. That means every time when a new user account is created on system my program will have to grant the permission to access that? Isn't their any simple solution for that. Please suggest

Shakti Singh Dulawat · Answer

Please follow the steps mentions here https://esha.zendesk.com/hc/en-us/articles/202977089-Granting-Access-to-Additional-Users-with-SQL-Server-Management-Studio-for-Genesis-Food-and-Food-Processor