Re: General permission question

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

  • Re: Execute Persmission denied on object sp_OACreate
    ... If so what access and permissions. ... The account is a windows account. ... One method to test permissions is to log in to your SQL Server box using the ... >>> SA account password and gaining access to the database. ...
    (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: Permissions
    ... >lusermgr.mcs lists of users and groups. ... >> member of the group should have same permissions. ... >groups and list out permissions of an account based on ...
    (microsoft.public.windowsxp.security_admin)
  • Re: Permissions
    ... in some ms articles, they mentioned 'everyone' account. ... lusermgr.mcs lists of users and groups. ... > member of the group should have same permissions. ...
    (microsoft.public.windowsxp.security_admin)
  • Re: Access database, network access problems
    ... It turns out it is a permissions problem, but, the cause is still unknown. ... If I was to replace this account with my own, the connection works, and I ... webserivce that returns data from a small access database; ... > engine will attempt to create an .LDB file. ...
    (microsoft.public.dotnet.framework.adonet)