Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Scripting Security for a user

Status
Not open for further replies.

jxc

IS-IT--Management
Jan 11, 2001
17
CA
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?
 
You could create a database role which allows access to only the 50 tables you specific, then assign users to the role.

--Angel [rainbow]
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.
 
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.
 
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
 
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 [rainbow]
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top