Wednesday, March 21, 2012

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

No comments:

Post a Comment