Re: SQL2005 grant user access to certain tables

Database role was the correct option for this situation.

An Application role is used to provide permissions to all users of an
application. The application has to execute a special stored procedure in
order to use the Application Role, and then the users are limited to the
permissions afforded by the application role. You may wish to read up on
Roles in Books OnLine.

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

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


<walanta> wrote in message
news:hjq1f2p6jpa9fj2dbk0cc8jnnmq4s1932u@xxxxxxxxxx
Should I create database role or application role.
I created database role and it seems like it's working. What's the
difference? Thanks

On Fri, 25 Aug 2006 13:37:21 -0700, "Arnie Rowland" <arnie@xxxxxxxx>
wrote:

REMOVE that user from the db_datareader role -that role allows reading ALL
tables.

Create a new Role, add the user to the new Role.
Then for each table that the user needs to read, give SELECT permission to
the new Role. (By doing it for the Role, if you need to allow another user
(vacations, etc.) to do the same thing, you just add the additional user
to
the same Role.)

Verify that the user is not a member of any other Role that has
permissions
to SELECT data from the tables.

Make sure that the 'Public' does not have SELECT permissions for any
tables -PUBLIC 'should' not have any permission for any activity for any
object.


.

Relevant Pages

  • RE: Roles based security 2005 versus 2000
    ... Right-Click on the Database Role and select Properties ... In the Properties Dialog select the SECURABLES page ... Product team remove this feature to optimize the ... active and if you try to grant the permissions again by select the ...
    (microsoft.public.sqlserver.security)
  • Re: Why securities disappear in SQL Server 2005 ?
    ... Do you consider that an enhancement to a 5 years old tool - Enterprise ... objects and it should show you permissions that you have already given on ... database role then from role securities tab i assign permissions to ... securities to users then everything is ok, ...
    (microsoft.public.sqlserver.security)
  • RE: how to deal with public role?
    ... All databases contain a public database role of which all users, ... roles are members. ... Do not grant additional permissions to this role. ...
    (microsoft.public.sqlserver.security)
  • Re: Why securities disappear in SQL Server 2005 ?
    ... objects and it should show you permissions that you have already given on ... I'm using SQL Server 2005 and MS Management Studio, ... database role then from role securities tab i assign permissions to ... securities to users then everything is ok, ...
    (microsoft.public.sqlserver.security)
  • Re: Why securities disappear in SQL Server 2005 ?
    ... grant the INSERT permission on the authors table to another user. ... PRINT 'You may not GRANT INSERT permissions on authors.' ... database role then from role securities tab i assign permissions to ... securities to users then everything is ok, ...
    (microsoft.public.sqlserver.security)