MS-Access Sikkerhed og MS-SQL

I'm not 100% sure I understand your problem! Normally a user is only given permissions to the objects he/she needs access to and NOT all objects. So if a table in SQL server is linked to an Access front-end then if the users doesnt have permissions they cant import/open the table!

Det er tildels også korrekt, men hvis brugeren har adgang til tabellerne, så kan vedkommende i pricippet tagen en anden accesdb og importere alle tabellerne til denne. Samtidig kunne det være ønskeligt at man kan bruge tabellerne, men ikke nødtvendigvis at brugeren skal se alt hvad der er i dem.

Var det mere forståelig? ;-)

If he has access to SQL Server objects then he can do anything he wants within the rights he has. This can be in Access/VB/ASP or what ever and there isnt much you can do about it. To stop him seing specific information in the tables then it you can use views. Then you give him permission to use the view and not the table

hows it going skovs?

Hej Terry

Faktisk havde du ikke helt ret. Det er muligt at indlæse en sql-database/tabel uden at den er synlig og dermed kan du også anvende SQL-auth. se bla:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/office97/html/connectionstrategies.asp

Not quite sure what you mean, can you give an example?

Hej Igen

Ja lad os antage at du bruger Access som frontend, men ikke vil lade brugerne få adgang til tabellerne før de har angivet kodeord.
Der ligger altså en tabel med brugerid og kodeord på sql-server.

Databasen åbnes og du bliver anmodet om kodeord. Du angiver og access laver et skjult opslag i en brugertabel på SQL-serveren. Du ved nu at brugeren har adgang til sql-serveren fordi vedkommende er blevet Windows godkendt, men du ved også hvilken bruger fordi vedkommende har tilkendegivet kodeord og brugerid. Hvis kodeord er forkert så lukkes forbindelsen til SQL-serveren.

Bruger som ikke er windows-godkendt kan godt åbne databasen, men vil aldrig få adgang til tabellerne fordi de ikke er godkendt.

Du bruger følgende til at åbne og så laver du en hardkodet gennemlæsning af tabellen med db.openrecordset

Sub PreConnect (strUserName As String, strPassword As String)
    Dim wrkRemote As Workspace, dbsRemote As Database
    Dim strConnect As String
   
    strConnect = "ODBC;DSN=MyServer;DATABASE=MyDatabase;" & _
        "UID=" & strUserName & "PWD=" & strPassword & ";"
    Set wrkRemote = DBEngine.Workspaces(0)
    Set dbsRemote = wrkRemote.OpenDatabase("", False, False, strConnect)
    dbsRemote.Close            ' Close database but keep connection.
End Sub

Var det bedre:

:o)
and how do you stop me from reading the code?

OK, this methid is fine if you want to utilize your own security, but you could just as well have a number of group permissions and then add users to these as needed. That way they only get to see what they are allowed to. In your case you would need to do it all by hand (code).
I've used both methods, and others, and in the end its really just a case of doing what one thinks best.
Access also has its own security, and here you can define what objects a user can have access to.

hi skovs, are we finished with this question?

Jeg ved ikke rigtig... jeg har ladt den stå åben fordi jeg stadig ikke helt har følt at dit svar var brugbart.

hi skovs
Your question has been open almost a month and only I have commented, so I very much doubt that there is going to come more input. And if your not happy with my answer then you can just place an answer yourself and accept and then you get your points back. We have over 1000 open questions in Access and it would be nice if we could "clean up" if you see what I mean.

You could also try using Access data projects (.ADP) which uses SQL server as teh backend.

I use Access (ADP) and I have also used MDB with linked tables and as I have no problems with security breaches.

Jeg lukker og giver dig en smule for din indsats :-)

Thanks
You could also take a look at Thomas's article on Access security. http://www.eksperten.dk/artikler/314
Not sure if it covers SQL Server connections but it gives infromation on how to keep people out of the code.