Limiting permissions to only specific tables

Question

Post reply

Limiting permissions to only specific tables

Isabelle2378

SSCertifiable

Points: 6174
More actions
September 4, 2007 at 10:52 am

#121450

Hi,
This might be a silly questions How can I limit the table access for a specific user. In Management Studio, I go into User Mapping and can set a default Schema, but not drill down to the specific tables within that schema. So I did it manually by grant select on [schema].
to the user, but would this only give them access to that one object?
What I need is for a user only to have read/write access to 3 specific tables and nothing else.
Thanks!
Isabelle
Thanks!
Bea Isabelle

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

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

noeld SSC Guru Points: 96590 More actions · Answer 1

>>> So I did it manually by grant select on [schema].

to the user, but would this only give them access to that one object? <<

Yes ... as long as it is not part of any "role" (other than public)

* Noel

noeld SSC Guru Points: 96590 More actions · Answer 2

you could check the permission by using has_perm_by_name as posted

here

* Noel

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 3

I am still having trouble with this. Even though I only gave them select to specific tables and have verified that they are not a part of any roles other than public, that account can still query all the other tables. I created the user using the Studio Manager and noticed that unless I went into the User Mapping section and clicked the database that these tables are in and check read access, it cannot see any tables at all. But by clicking this, it gives the account read access to everything. If I uncheck it, then I cannot grant it read to a specific table in that database. I get the following error:

GRANT ACCESS ON DBO.XREF_MOVEX_FF TO USER

Msg 15151, Level 16, State 1, Line 1

Cannot find the object 'XREF_MOVEX_FF', because it does not exist or you do not have permission.

If I go back and check it, then it goes through with no errors but the user can see everything!

Thanks!
Bea Isabelle

noeld SSC Guru Points: 96590 More actions · Answer 4

Isablelle,

Unless the tables on the database have explicitly been granted "SELECT" permissions to the "public" role you should receive a message like this:

Msg 229, Level 14, State 5, Line 2

The SELECT permission was denied on the object 'TABLENAME', database 'DATABASENAME', schema 'dbo'.

To verify that is the case you can run:

REVOKE SELECT ON OBJECT::dbo.XREF_MOVEX_FF FROM public

-- there should be no read permissions in it for that user

-- to give permissions explicitly you can use:

GRANT SELECT ON OBJECT::dbo.XREF_MOVEX_FF TO Username

hope this helps

* Noel