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.

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
    ... 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 ... GRANT SELECT ON PDF TO briank ...
    (microsoft.public.sqlserver.setup)
  • Re: Complete Neophyte Question(s)
    ... No you cannot remove a login from the 'public' role. ... For rights to _use_ objects then the appropriate rights need to be granted ... GRANT SELECT ON dbo.Orders TO OrderViewers ... for a database that is supposed to be secured it is a bad idea ...
    (microsoft.public.sqlserver.security)
  • 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: Public Role
    ... >What is the purpose of having a PUBLIC role? ... Since all users are always members of PUBLIC, any rights you want to ... grant to everyone can simply be granted to PUBLIC. ...
    (microsoft.public.sqlserver.security)
  • Set identity_insert
    ... i have a login with role who have rights ... Grant Select, update, insert, delete, view definition to onLine --online is ...
    (microsoft.public.sqlserver.security)