Permission using T-SQL

[AnnetteBean]

I have a database with over 100 tables. I need to add a user with deny permission on all tables except one. Is there a way of doing this without having to enter the names of all the tables and not using EM and ticking over 100 boxes? I tried to write the script using a select from sysobjects where xtype = 'U' but it didn't like it very much!!

Any help would be greatfully appreciated.

Many thanks

[rmiao]

The only thing need to do is leave the user in public role only and grant permission on that table either in em or with grant statement.

[AnnetteBean]

Thanks for that, but I would have thought that if Public had select on all tables, by default my new user would. If I give public deny on all tables that will overwrite any permissions given on the individual login as deny supercedes any select. Maybe I'm missing something here.

[rmiao]

Public role doesn't have any permission on user tables by default.

[Rawhide]

Select 'Deny All On [' + Table_Schema + '].[' + Table_Name + '] To UserAccount'
From Information_Schema.Tables
Where Table_Type = 'BASE TABLE'
And Table_Name <> 'dtproperties'
And Table_Name <> 'The table to which I want to allow access'