Help for a newbie plz!!!! Permissions for a User

Question

Help for a newbie plz!!!! Permissions for a User

Javier Rosado

SSC Rookie

Points: 34
More actions
June 8, 2005 at 3:48 pm

#89389

Hi all!
I'm here again to bother you all with my stupid questions. I hope you understand i'm learning about SQL Server Administration basics. My question is the following:
is there any way via sql scripting to deny permissions to every table and view except one for a specific Database User???. I've been asked to do this and i'm sure there's a way, but i can't imagine what is it...any idea?. What i'm looking for is than even if i register a server with that user, any table or view but the one i've chosen is visible for him...
TNX 2 ALL

Viewing 10 posts - 1 through 9 (of 9 total)

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

SteveB-74825 Hall of Fame Points: 3290 More actions · Answer 1

not sure what you mean exactly but you could add the user as 'public' in the database and then grant them permissions against the table you want them to see (select etc). doing this via the GUI is pretty easy and for the SQL look in BOL (grant etc)

oh and by the way, your question isn't stupid. if you don't ask you'll never know 😉

cheers

dbgeezer

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Hmm, if he doesn't have access to the tables, nor the views, can he use stored procs??

Cause if he can't use procs, I'd simply deny them access to the db altogether (actually not grant it ).

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

If it's just one single user for one single table (how odd! maybe I don't understand correctly..) then the GUI is easy...

Otherwise I would create a role - grant/deny permissions & then add the user...

btw, via script you can: "GRANT SELECT, INSERT, UPDATE, DELETE ON table TO JSmith"

GO

hope this is what you're looking for!

**ASCII stupid question, get a stupid ANSI !!!**

cliffb SSCarpal Tunnel Points: 4598 More actions · Answer 4

sushila's solution is probably the right way to go, but since you asked about scripting, here is the syntax.

DENY ALL ON tablename TO username

This will effectively deny all permissions on the object for the specific user. You will need to do this for each table.

GRANT SELECT ON tablename TO username

That should do the trick.

Rick Lowrey Right there with Babe Points: 785 More actions · Answer 5

You may also wish use the EM GUI to check and see if SELECT permissions have been granted to 'public' for all tables in the database. If they have, then revoke them first. Then grant the permissions you want for the users you want.

Javier Rosado SSC Rookie Points: 34 More actions · Answer 6

Hello all!

After a while of hard brain-work i've reached same solution than cliffb and it seems to work OK.

Thnx 2 All

PS: I'm getting quite pretty help from this site, and i think i'll be a regular here.

SteveB-74825 Hall of Fame Points: 3290 More actions · Answer 7

doesn't public just let you see the db is there ????

didn't think it granted any permisssions at all ?

cheers

dbgeezer

SteveB-74825 Hall of Fame Points: 3290 More actions · Answer 8

if you liked that wrapping it in sp_msforeachtable should save you some time ...

cheers

dbgeezer

Rick Lowrey Right there with Babe Points: 785 More actions · Answer 9

You can grant permissions to 'public'. This has the effect of letting everyone who has access to the database (via sp_grantdbaccess or GRANT statement) inherit that permission. Very useful for reporting stored procedures when you don't want your users to have direct access to the tables.