Re: Removing Permissions from the public role

The OP indicated EM was NOT to be used, therefore it is a good assumption
that the version is 2000.

You could cycle through the list generated from INFORMATION_SCHEMA.TABLES
and REVOKE any SELECTs to PUBLIC. That would require a simple dynamic SQL
statement in a loop.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf


"Jay B" <jayB@xxxxxxxxxxx> wrote in message
news:bLIah.69$Gs5.27@xxxxxxxxxxxxxxx
did you think about putting those 8 tables in a different schema, and then
just granting rights to that schema, revoking the rights for that group
from public.

BrianK wrote:
I have to allow another application a connection to my database and
only grant them select rights to about 8 tables out of a possible 150
tables in this database. I created a new login and added a new role for
that user and ran some grant scripts to allow them to select from the 8
tables they need to select data from. I see that there is a public role
added by default for all new logins that are created. It seems as if
the public security role is still allowing the new login access to all
of the tables in the database. Rather then have 150 lines of T-SQL to
revoke the select permission from the public role is there an easier
way to do this (NOT THROUGH EM ONLY T-SQL). Below are the scripts that
i used to create the new login:

sp_addlogin 'briank','briank','Northwind'

sp_addrole 'lookup'

sp_grantdbaccess 'briank','briank'

sp_addrolemember 'lookup',briank

GRANT SELECT ON PDF TO briank

GRANT SELECT ON SOCIAL TO briank


Any insight would be appreciated



.

Relevant Pages

  • Re: Removing Permissions from the public role
    ... did you think about putting those 8 tables in a different schema, and then just granting rights to that schema, revoking the rights for that group from public. ... that user and ran some grant scripts to allow them to select from the 8 ... I see that there is a public role ... the public security role is still allowing the new login access to all ...
    (microsoft.public.sqlserver.setup)
  • Removing Permissions from the public role
    ... tables in this database. ... that user and ran some grant scripts to allow them to select from the 8 ... I see that there is a public role ... the public security role is still allowing the new login access to all ...
    (microsoft.public.sqlserver.setup)
  • Re: No db access after publishing web site
    ... GRANT UPDATE TO ... If I detach and attach this database on a different PC (according that PC ... Cannot open database "pago" requested by the login. ... Are you detaching/attaching the SQL Server Express database correctly ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Allowing Anonymous write access only.
    ... need at least READ permission for login. ... > been set up so that anonymous FTP users have write access only, this> may seem insecure and we do get a certain ammount of hackers or> taggers testing the system by dropping test files and folders onto the> server, but because anonymous users do not have read access they soon> find that they cannot download anything they upload and go elsewhere. ... This is where my problems have started,> I initialy replicated all the IIS setting and NTFS permission from my> NT box on my 2003 box but so far have been unable to achive the same> result, it appaers that I can only grant anonymous write access if I ...
    (microsoft.public.inetserver.iis.ftp)
  • Re: Simple SQL Security Question From SQL Newbie
    ... >exec sp_grantdbaccess 'WIN2K\ASPNET' ... >VS.NET, I can use the server explorer and I can see the database, it's ... >login 'WebCalendar'. ... I also did a GRANT ALL on y2003 TO ASPNET ...
    (microsoft.public.sqlserver.security)