Scripting Security for a user

[jxc]

I have a database which contains in excess of 1200 tables. I would like to setup a scripting model which will prevent some users from accessing all but 50 tables. Is there a really quick way to do this?

 

[JohnDTampaBay]

You could create a database role which allows access to only the 50 tables you specific, then assign users to the role.

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[vadimg]

It's easier to manage database roles rather then individual users.

Create a database role in Enterprise Manager (in Databases -> DB name -> Roles).
Define permissions for that role (example: SELECT on the 50 tables you've mentioned).
Then add the users to a this role.

hope this helps.

 

[jxc]

I have setup a role as described but for some reason when using Access via an ODBC connection my users still have permissions to tables that I have removed.....Access issue???

Thanks
/jxc

 

[JohnDTampaBay]

Hmm. Check the ODBC connection for the user name used to connect to SQL. Make sure that you are using integrated security.

Make sure that the individual users have not been granted access to the tables. I believe individual permissions supercede role permissions.

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.