Re: General permission question

Thanks for the replies. After further investigation, making the users go
through the steps again I discovered that my permissions were fine. They were
logging into the PC with a different user that I wasn't aware of.


"Dan Guzman" wrote:

My explanation for this is that SQL Server 2005 uses "most restrictive
permissions win". Is this accurate?

This is not exactly correct. Database permissions are cumulative except
that DENY takes precedence over GRANT.

The account establish security credentials before database permissions can
be evaluated, . If the account is a member of a Windows group that is a
sysadmin server role member, then the account connects with sysadmin rights.
In other cases where the access is via Windows group membership and the
account is a member of multiple domain groups, I believe SQL Server uses
only the first group for security credentials. You might try executing
xp_logininfo to see if the permission path displayed matches your
observations.

Personally, I don't grant permissions directly to Windows groups. Instead,
I grant permissions only to database roles and control permissions via role
database role membership.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Snowmizer" <Snowmizer@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E734951E-921A-4760-98B9-45497F835A6A@xxxxxxxxxxxxxxxx
Here's my scenario:

I have a user in my Active Directory. This user is a part of two different
groups in AD. These AD groups have a logon in my SQL Server 2005 database.
These two groups both have rights to a particular table in my database.
One
group has select only rights and the other has select, insert, update
rights
on the same table.

I have an application that is accessing this table (updating data in the
table). When my user tries to run this application they are getting a
message
that they don't have "Update" permissions on the table. This despite the
fact
that even though they are a member of the group that only has select
permissions they are also a member of the group that has select, insert,
and
update permissions.

I also have this same scenario on my SQL Server 2000 database and things
work fine.

My explanation for this is that SQL Server 2005 uses "most restrictive
permissions win". Is this accurate?

Thanks.


.

Relevant Pages

  • ADP, Application Role, and objects
    ... The above link is to an atricle on how to implement SQL Server Application ... After you connect with your ADP, fire a bit of code to set the ... third party tools to view the data on the same database. ... Scenario 1 - If I explicitly grant permissions on that object to the user ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Execute Persmission denied on object sp_OACreate
    ... > SQL Server is creating a job behind the scenes. ... > permissions. ... > SA account password and gaining access to the database. ... >>> How can get a user permissions to execute these stored procedures ...
    (microsoft.public.sqlserver.security)
  • Re: SQL Server 7.0 ignores user permissions
    ... Is Windows user a member of some Windows groups? ... have all permissions in SQL Server. ... > In my database I have a table called, for the sake of argument, 'TableX'. ...
    (microsoft.public.sqlserver.security)
  • Re: Newbie: I dont understand user permissions for table access
    ... > My database is remote to my workstation. ... > tables/fields WITHOUT specifying anything in the permissions dialogs? ... >> HOW are you connecting to SQL Server? ... what rights/permissions have been granted to the PUBLIC role? ...
    (microsoft.public.sqlserver.server)
  • Re: Execute Persmission denied on object sp_OACreate
    ... SQL Server is creating a job behind the scenes. ... SA account password and gaining access to the database. ... > SQL Server doesn't check permissions on indirectly referenced objects as ... > the proxy account security context for non-sysadmin users from Enterprise ...
    (microsoft.public.sqlserver.security)