Permissions within groups

Running SQL Server 2000 under Windows 2003 server. All
updates and service packs applied. Using Windows
Authentication via group membership. No individual users
are defined in SQL Server. Some users can update tables,
while other users in the same group cannot (permission
denied).
I'm baffled...These people are probably members of another group that has been denied
those rights. Remember that permissions are cumulative except that deny
trumps...
"Jay Varner" <dimsjay@.dims-vote.com> wrote in message
news:e55901c3f0e8$da00f2c0$a301280a@.phx.gbl...
> Running SQL Server 2000 under Windows 2003 server. All
> updates and service packs applied. Using Windows
> Authentication via group membership. No individual users
> are defined in SQL Server. Some users can update tables,
> while other users in the same group cannot (permission
> denied).
> I'm baffled...|||Try comparing users that can update vs those that can't using gpresult.
321709 HOW TO: Use the Group Policy Results Tool in Windows 2000
http://support.microsoft.com/?id=321709
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||I appreciate your quick reply, Don, but am not sure I expressed the
problem sufficiently.
Using Active Directory in W2K3 server, we defined specific user groups
for each of three domains that need to access the database. Each user
appears in only one group. All of these groups have been added to SQL
Server, and all have the same rights to the database(s). The problem
seems to occur for several people in each group, and does not seem to be
related to any permissions they are allowed on the domain. For
instance, in one of the groups, one user is a standard DOMAIN USER in
Windows, and he is able to perform updates to any of the tables in the
database; another user is a DOMAIN ADMIN who belongs to the same group,
but he is denied access to perform updates.
If we assign the group SYSTEM ADMINISTRATOR priveleges on the database,
it seems to resolve the problem, but thats not an acceptable resolution
for this large office.
If we assign each individual user (rather than the groups) to SQL, the
problem goes away. Again, this is a large office, and they would like
to avoid the additional overhead of having to add each new user to both
Windows and SQL Server.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

Permissions not applied

Hi everyone,
I'm facing a weird problem trying to set permissions on a column in Sql
Server 2000. When using Enterprise Manager the permission on some columns
don't get applied, that is setting Select permissions on Column1, Column2 an
d
Column3 only saves the permission on Column1 and Column2 but not on Column3.
I tried setting the permissions with a GRANT script in the Query Analyzer,
but still without any luck.
Service Pack 4 has been applied to SQL Server 2000.
Has anyone had the same type of troubles setting permissions on columns?
TIA,
Mogens NielsenMogens Nielsen - WM-Data A/S
(MogensNielsenWMDataAS@.discussions.microsoft.com) writes:
> I'm facing a weird problem trying to set permissions on a column in Sql
> Server 2000. When using Enterprise Manager the permission on some
> columns don't get applied, that is setting Select permissions on
> Column1, Column2 and Column3 only saves the permission on Column1 and
> Column2 but not on Column3. I tried setting the permissions with a GRANT
> script in the Query Analyzer, but still without any luck.
Would it be possible for your to post the CREATE TABLE statement for
the table, and the GRANT statements you are using?
It would also be interesting to know how you conclude that the
permission is not applied.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi
I did some testing but not usimg EM and it seems to be worked fine
use demo
create table test (c1 int,c2 int,c3 int)
go
grant select on test to test --test no syadmin,db_owner
go
deny select on test (c3) to test
--connected as test
select * from test
--Server: Msg 229, Level 14, State 5, Line 1
--SELECT permission denied on object 'test', database 'demo', owner 'dbo'.
select c1,c2,c3 from test
--Server: Msg 229, Level 14, State 5, Line 1
--SELECT permission denied on object 'test', database 'demo', owner 'dbo'.
select c1,c2 from test
--works fine
"Mogens Nielsen - WM-Data A/S"
<MogensNielsenWMDataAS@.discussions.microsoft.com> wrote in message
news:FC8AFD90-255F-4942-BC78-BE674669A1FE@.microsoft.com...
> Hi everyone,
> I'm facing a weird problem trying to set permissions on a column in Sql
> Server 2000. When using Enterprise Manager the permission on some columns
> don't get applied, that is setting Select permissions on Column1, Column2
> and
> Column3 only saves the permission on Column1 and Column2 but not on
> Column3.
> I tried setting the permissions with a GRANT script in the Query Analyzer,
> but still without any luck.
> Service Pack 4 has been applied to SQL Server 2000.
> Has anyone had the same type of troubles setting permissions on columns?
> TIA,
> Mogens Nielsen|||Hi Uri and Erland,
thank you both for trying to solve my problems... :-)
The table consists of more than 350 columns, which may be the cause. I don't
know, really. As a solution I created a view on a subset of the columns
instead, in which I've managed to set the permissions.
So problem solved or at least avoided...
Regards,
Mogens
"Uri Dimant" wrote:

> Hi
> I did some testing but not usimg EM and it seems to be worked fine
> use demo
> create table test (c1 int,c2 int,c3 int)
> go
> grant select on test to test --test no syadmin,db_owner
> go
> deny select on test (c3) to test
> --connected as test
> select * from test
> --Server: Msg 229, Level 14, State 5, Line 1
> --SELECT permission denied on object 'test', database 'demo', owner 'dbo'
.
> select c1,c2,c3 from test
> --Server: Msg 229, Level 14, State 5, Line 1
> --SELECT permission denied on object 'test', database 'demo', owner 'dbo'
.
> select c1,c2 from test
> --works fine
>
>
>
> "Mogens Nielsen - WM-Data A/S"
> <MogensNielsenWMDataAS@.discussions.microsoft.com> wrote in message
> news:FC8AFD90-255F-4942-BC78-BE674669A1FE@.microsoft.com...
>
>sql

permissions hierarchy

Hi,
With Yukon the permissions which are applicable to a securable can also
be applied at parent level. In case of database the permissions are
applicable to all schemas, all objects in that schemas. On the database
permissions tab there are two permissions 'Alter' & 'Alter any schema'. I
guess this is to allow just the 'alter' on schema but not ont all other
types e.g. assemblies. But then why not 'control any schema'
Thanks.
AjeyPlease post Yukon/SQL Server 2005 questions to the beta newsgroup for Yukon.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.