May 14, 2005 at 4:06 pm
hi,
My system..
MS Access (MDB not ADP) client on SQL server 2000. Connection from Client to server I wish to be "Trusted Connection" for SystemDSN and ADO connection strings.
So if I have (say) 20 people who can access the client, but I only want 5 to access to the server in any form, the remaining to kicked out as they do not have server ID's. How do I achieve this ? I thought I could just place the 5 ysers in a user group with correct permissions, and that would be it ? They other 15 would be kicked out.
I know I can use "IS_MEMBER" in stored procedure code, but this wont stop all access to the server.
Are there any papers on Trusted connections, and limiting those trusted connections once they logon to there server ?
Thanks
May 15, 2005 at 1:17 pm
I found this link..
http://www.developer.com/tech/article.php/10923_721441_3
So I guess I do this..
1) Use NT Windows trusted security
2) Turn guest account off, by deleting it.
3) Create a accounts/groups for my USERS, with asscociated permissions.
4) Have one account for developer, with full permissions.
5) Place my users in correct accounts.
Done. Am I missing anything.
Am I missing anything. This setup means if a NT user opens my MS Access database(mdb) with (ie no separate logon to the MS Access database) and activates a SQL connection ( ie either a link or a ADO connection) one of two things will happen.
a) If a user is in one of the accounts, connection Ok.
b) If use not found in one of the accounts, Connection fails.
Question : Can anybody confirm this, or offer further advice ?
Thanks
May 16, 2005 at 9:12 am
The 5 users with valid NT SQL DB accounts will have access to the linked SQL objects. The remainder of the users will have access only to the remaining non-SQL objects, eg. local tables or queries that use local tables only.
May 16, 2005 at 1:22 pm
.."eg. local tables or queries that use local tables only. "..
By Local tables you are refering to those tables in the MS Access MDB.
May 27, 2005 at 3:22 pm
Nope I got it wrong above.. do this
1) Use NT Windows trusted security for entry into the server
2) Make sure PUBLIC 'database role' permissions are turned off (see below) for in my database.
3) Create a 'database role' for my USERS, with asscociated permissions.
4) Have one user in db_Owner for developer, with full permissions.
5) Place my users in correct database roles.
Turning all permissions ( ie not touching system and standard SQL server stuff, only my database objects) off on the public 'database role', means that any who have NT Windows trusted security access to the server will not be able to do anything within your database without direct permission. All uses who get onto the server and then a database first go thru the PUBLIC 'database role' of the database (also see guest user below). If there are no permissions there, then the search is on via other 'database role' of the database for that users ID, if no match, no permissions for the user.
Note : If a 'guest user' exists in a database, this allows users with NO valid database logon, but with a valid SQL Server logon, to get access to database and use the public 'database role' of the database. If there is no guest user then access is rejected. Also the 'guest user' cant be deleted from the master database. Refer link : http://www.developer.com/tech/article.php/721441
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply