SQL 2000 New User: Explicit Deny All but one table

[mitchelt]

Hi,

On SQL 2000 using SQL Authentication I created a new user and had to give them access to only 1 table. The "problem" is that there are a lot of tables, views and stored procedures that I had to one-by-one select explicit deny (RED X) on Select/Insert/Update/Delete and Exec on the SP's.

As you can imagine this is very tedious, is there a better way? This is a remoted SQL Server that cannot use Windows Authentication.

Any help or tips would be VERY appreciated.

Thanks!

Mitch

 

[MichaelF81]

Give the user overall restrict rights on the database (via SQL Enterprise Manager) and then give them rights to the 1 table only.

1 example

/* Deny permissions to SELECT. */
DENY SELECT
ON authors
TO Bob
GO

another example

/* Revoke permissions to SELECT. */
REVOKE SELECT
ON authors
FROM Bob
GO

"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[MichaelF81]

You are want to deny/revoke (I can not remember which) on select so the DB is still accessible to the user.




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[mitchelt]

THANKS!!!

Do you know if something like this would work:

DENY Select, Insert, Update, Delete
ON authors
TO Bob
GO

Mitch

 

[MichaelF81]

That should work, if you get an error try

DENY Select, Insert, Update, Delete
ON authors
TO [Bob]
GO

"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[mitchelt]

OK,

this works:

DENY Select, Insert, Update, Delete
ON TABLENAME
TO USERNAME
GO

I ran it in Query Analyzer and it works table by table, is it possible to apply the above to an entire Database in one shot?

Thanks,

Mitch

 

[gmmastros]

There is an undocumented system stored procedure that may help.

sp_msforeachtable 'DENY Select, Insert, Update, Delete 
                   ON ?
                   TO USERNAME'

Essentially, sp_msforeachtable will run for each table in the system. The ? mark in the query will be replaced by each table name.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[MichaelF81]

O cool, I did not do that, after that you can then run

GRANT Select, Insert, Update, Delete
ON TABLENAME
TO USERNAME
GO

to give access to required tables




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[mitchelt]

George,

SWEET!!! Worked perfectly for all of the USER tables. But it did not update the SYSTEM tables, seems like I should deny access to those also? Anyway to do that?

MichaelF81...thank you for your help also!

Thanks!

Mitch

 

[gmmastros]

Which system tables are you concerned about?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[MichaelF81]

mitchelt,

Make a back-up of the DB first, then it is worth a shot, I am not sure myself.

Your welcome btw, I am just here trying to imporve my skills and learn too!




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[mitchelt]

George,

The system tables I am talking about would be the ones that are created automatically when you create a new DB.

I would think I would not want a user who is restricted to one single USER table to be able to mess around with the system tables?

Thanks,

Mitch