Database Access Problem with Trusted Source
-
I am trying to set up an application which will use Trusted Source connections to SQL Server.
When the users want to add a user to the database, they add a record to a table (using a web screen). A job runs periodically and picks up the new record in the table and goes through the following steps:
1. Adds the user to the server (login name is DOMAIN\USERID) using SP_GRANTLOGIN.
2. Adds the user to the database using SP_GRANTDBACCESS
3. Adds the user to the role within the database using SP_ADDROLEMEMBER.
This appears to work OK but if I look at the users for the database, the new user's database access is 'Via Group Membership' whereas users added manually through EM have database access of 'Permit'.
The new user cannot connect to the database through the application and gets an 'Invalid Connection String' error.
Any ideas what is going wrong?
Thanks
Jeremy
-
First, I would verify if 'Via Group Membership' group itself has all necessary accesses "Permit'.
alex
-
Thanks for the info.
I have just found the problem. I discovered that when I checked to see whether the user already had Database access, I was getting a false result - my SQL was giving the answer that the user already had access to the database when in fact they did not have access. My SP would then skip the sp_grantdblogin step and would then add the user to a role in the database. This resulted in the user having 'Via Group Permission' access.
It seems strange that SQL Server will allow me to add a user to a role in a database without granting the user access to the database - there is probably something I don't understand about Security Administration
Jeremy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply