best way to add security

Question

best way to add security

jessica-321853

SSCrazy

Points: 2207
More actions
April 27, 2006 at 9:32 am

#67418

hi I am doing a backend migration to sql server 2000 to my access 2000 databases, only my tables will be migrated into sql server, my question is i already added users with their permissions to my sql server database, now for my queries and reports, i would also like to add security for example, i want only selected users to be able to run some of the reports then i think I have to do this in the access database level correct? how do i do this? do i have to add a system workgroups? if so how?

help!!
thanks !!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

jfmccabe SSCrazy Points: 2547 More actions · Answer 1

Depends on how you're doing it.

If you put your "queries" / Views on SQL server than that's where the security goes. That's the recommended model - all calculation on the server, all display on the client. If you put your queries in Access - NOT recommended, then that's where the security goes. In that case, it down to workgroups and other such sillinesses.

If you put the Views on the server, then it doesn't matter about Access permissions, because if users attempt to run reports based on views, they will get an ODBC error.

One simplification is to assign security via NT groups rather than to individual users. This means you define e.g. an Accounts Group, a Sales Group and so on. Each group has specific access rights to Views. NOT to tables - because that gives you better control. You then have a server based table which indicates (roughly) what each group can do. Your Access front end reads this table and the user's group membership(s) and then decides what things / buttons / menu items to display so that the user doesn't get lots of ODBC errors when they attempt to do silly things.

jessica-321853 SSCrazy Points: 2207 More actions · Answer 2

thanks for your advice, to add security via the NT groups.. is this just going to security in enterprise and creating a new group?

jfmccabe SSCrazy Points: 2547 More actions · Answer 3

Basically, you create the groups in Active Directory and then add users again in Active Directory.

In SQL server, you need to be a member of sysadmin or security admin or higher) to add NT groups, and you do this in the security section of the instance and add the group as a new Login. Finally, in the database you want to use, you add the group as a user and assign permissions.