Apply policy to restrict records

Hello,
It is possible to restrict the records viewed by an user
using policies instead of creating a view?
If it is possible, how can i do it?
Best regards
CC
You cannot restrict 'some rows' from viewing without creation of view.
You can deny SELECT on the whole table not just on some rows.
Why do you want to that without a view? It is a classic for such kind of
operations.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1cead01c45399$fdffadb0$a301280a@.phx.gbl...
> Hello,
> It is possible to restrict the records viewed by an user
> using policies instead of creating a view?
> If it is possible, how can i do it?
> Best regards
|||Hi Uri,
Someone ask me this situation and i told him that it
wasn't possible to do it but... never knows and i need to
ask it.
Thanks a lot,
Best regards

>--Original Message--
>CC
>You cannot restrict 'some rows' from viewing without
creation of view.
>You can deny SELECT on the whole table not just on some
rows.
>Why do you want to that without a view? It is a classic
for such kind of
>operations.
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1cead01c45399$fdffadb0$a301280a@.phx.gbl...
>
>.
>

Apply policy to restrict records

Hello,
It is possible to restrict the records viewed by an user
using policies instead of creating a view?
If it is possible, how can i do it?
Best regardsCC
You cannot restrict 'some rows' from viewing without creation of view.
You can deny SELECT on the whole table not just on some rows.
Why do you want to that without a view? It is a classic for such kind of
operations.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1cead01c45399$fdffadb0$a301280a@.phx.gbl...
> Hello,
> It is possible to restrict the records viewed by an user
> using policies instead of creating a view?
> If it is possible, how can i do it?
> Best regards|||Hi Uri,
Someone ask me this situation and i told him that it
wasn't possible to do it but... never knows and i need to
ask it.
Thanks a lot,
Best regards
>--Original Message--
>CC
>You cannot restrict 'some rows' from viewing without
creation of view.
>You can deny SELECT on the whole table not just on some
rows.
>Why do you want to that without a view? It is a classic
for such kind of
>operations.
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1cead01c45399$fdffadb0$a301280a@.phx.gbl...
>> Hello,
>> It is possible to restrict the records viewed by an user
>> using policies instead of creating a view?
>> If it is possible, how can i do it?
>> Best regards
>
>.
>

Apply policy to restrict records

Hello,
It is possible to restrict the records viewed by an user
using policies instead of creating a view?
If it is possible, how can i do it?
Best regardsCC
You cannot restrict 'some rows' from viewing without creation of view.
You can deny SELECT on the whole table not just on some rows.
Why do you want to that without a view? It is a classic for such kind of
operations.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1cead01c45399$fdffadb0$a301280a@.phx
.gbl...
> Hello,
> It is possible to restrict the records viewed by an user
> using policies instead of creating a view?
> If it is possible, how can i do it?
> Best regards|||Hi Uri,
Someone ask me this situation and i told him that it
wasn't possible to do it but... never knows and i need to
ask it.
Thanks a lot,
Best regards

>--Original Message--
>CC
>You cannot restrict 'some rows' from viewing without
creation of view.
>You can deny SELECT on the whole table not just on some
rows.
>Why do you want to that without a view? It is a classic
for such kind of
>operations.
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1cead01c45399$fdffadb0$a301280a@.phx
.gbl...
>
>.
>

Application Roles

Hi
I'm using an application role to restrict acess to the users to a Database
The Application role is always activated without any problem.
Nevertheless I'm receiving permission error messages ("XXXX permission denie
d
on object 'TABLE', database...") when inserting, updating or deleting
records if I open a recordset before this operations, otherwise everything
works fine.
Below is an example of this,
If I call the following code before I trie to INSERT a record in TABLE2 I
receive the permission error message
..
Set rstRecordset = New ADODB.Recordset
rstRecordset.Open "SELECT * FROM TABLE1", cnnConn
..
If I don't call the previous code the INSERT works ...
(The AppRole have SELECT, INSET, UPDATE and DELETE permissions on TABLE1 and
TABLE2)
Do someone have any idea why this behavior ?
I'll apreciate any help.
Many Thanks
Daniel
EXAMPLE:
--
Private Sub CommandButton1_Click()
On Error GoTo ErrorHandler
Dim cnnConn As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Dim cmdCommand As ADODB.Command
Set cnnConn = New ADODB.Connection
With cnnConn
.Open _
"Provider=SQLOLEDB;Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=DBx;Data Source=SERVERx"
End With
'The AppRole is activated without problems--
Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = cnnConn
With cmdCommand
.CommandText = "Exec sp_setapprole AppRole, { Encrypt N Password} ,
'odbc'"
.CommandType = adCmdText
.Execute
End With
'IF this code is not called the INSERT bellow works fine, otherwise not--
--
--
Set rstRecordset = New ADODB.Recordset
rstRecordset.Open "SELECT * FROM TABLE1", cnnConn
'INSERT record on TABLE2--
With cmdCommand
.CommandText = "INSERT INTO TABLE2 (tipo, departamento, estado) VALUES
('A', 'XXX', 'P')"
.CommandType = adCmdText
.Execute
End With
..
ErrorHandler:
' clean up
End SubDid you turn pooling off in your connection string? If not, then
another connection is being opened under the covers and in that
connection the approle is not active. There's more information at
http://support.microsoft.com/defaul...;en-us;Q229564.
--Mary
On Tue, 05 Sep 2006 13:42:54 GMT, "Daniel Rodrigues" <u26179@.uwe>
wrote:

>Hi
>I'm using an application role to restrict acess to the users to a Database
>The Application role is always activated without any problem.
>Nevertheless I'm receiving permission error messages ("XXXX permission deni
ed
>on object 'TABLE', database...") when inserting, updating or deleting
>records if I open a recordset before this operations, otherwise everything
>works fine.
>Below is an example of this,
>If I call the following code before I trie to INSERT a record in TABLE2 I
>receive the permission error message
>..
>Set rstRecordset = New ADODB.Recordset
>rstRecordset.Open "SELECT * FROM TABLE1", cnnConn
>..
>If I don't call the previous code the INSERT works ...
>(The AppRole have SELECT, INSET, UPDATE and DELETE permissions on TABLE1 an
d
>TABLE2)
>Do someone have any idea why this behavior ?
>I'll apreciate any help.
>Many Thanks
>Daniel
>EXAMPLE:
>--
>Private Sub CommandButton1_Click()
>On Error GoTo ErrorHandler
>Dim cnnConn As ADODB.Connection
>Dim rstRecordset As ADODB.Recordset
>Dim cmdCommand As ADODB.Command
>Set cnnConn = New ADODB.Connection
>With cnnConn
> .Open _
> "Provider=SQLOLEDB;Integrated Security=SSPI;" & _
> "Persist Security Info=False;" & _
> "Initial Catalog=DBx;Data Source=SERVERx"
>End With
>'The AppRole is activated without problems--
>Set cmdCommand = New ADODB.Command
>Set cmdCommand.ActiveConnection = cnnConn
>With cmdCommand
> .CommandText = "Exec sp_setapprole AppRole, { Encrypt N Password}
,
>'odbc'"
> .CommandType = adCmdText
> .Execute
>End With
>'IF this code is not called the INSERT bellow works fine, otherwise not--
--
>--
>Set rstRecordset = New ADODB.Recordset
>rstRecordset.Open "SELECT * FROM TABLE1", cnnConn
>'INSERT record on TABLE2--
>With cmdCommand
> .CommandText = "INSERT INTO TABLE2 (tipo, departamento, estado) VALUES
>('A', 'XXX', 'P')"
> .CommandType = adCmdText
> .Execute
>End With
>..
>ErrorHandler:
> ' clean up
>End Sub|||Hello Mary
Yes.
I used in connection string "OLE DB Services = -2" and I also tried with
"Pooling=’False " (despite I think this is only for .Net and I'm tried wit
h
VB and Delphi6 with the same results.
I already read the KB article you mentioned.
Is there any other way to deactivate pooling ?
The only way I found to solve the problem was with one connection for the
selects and another one only for INSERT, UPDATE and DELETE statments.
By the way, i did't mentioned in my previous mail, I'm using SQLServer 2K
with SP4 and acessing with ADO using Delphi6 aplications.
Thanks for your answer
Best regards
Daniel
Mary Chipman [MSFT] wrote:[vbcol=seagreen]
>Did you turn pooling off in your connection string? If not, then
>another connection is being opened under the covers and in that
>connection the approle is not active. There's more information at
>http://support.microsoft.com/defaul...;en-us;Q229564.
>--Mary
>
>[quoted text clipped - 68 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200609/1

Application Roles

Hi
I'm using an application role to restrict acess to the users to a Database
The Application role is always activated without any problem.
Nevertheless I'm receiving permission error messages ("XXXX permission denied
on object 'TABLE', database...") when inserting, updating or deleting
records if I open a recordset before this operations, otherwise everything
works fine.
Below is an example of this,
If I call the following code before I trie to INSERT a record in TABLE2 I
receive the permission error message
..
Set rstRecordset = New ADODB.Recordset
rstRecordset.Open "SELECT * FROM TABLE1", cnnConn
..
If I don't call the previous code the INSERT works ...
(The AppRole have SELECT, INSET, UPDATE and DELETE permissions on TABLE1 and
TABLE2)
Do someone have any idea why this behavior ?
I'll apreciate any help.
Many Thanks
Daniel
EXAMPLE:
--
Private Sub CommandButton1_Click()
On Error GoTo ErrorHandler
Dim cnnConn As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Dim cmdCommand As ADODB.Command
Set cnnConn = New ADODB.Connection
With cnnConn
.Open _
"Provider=SQLOLEDB;Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=DBx;Data Source=SERVERx"
End With
'The AppRole is activated without problems--
Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = cnnConn
With cmdCommand
.CommandText = "Exec sp_setapprole AppRole, { Encrypt N Password} ,
'odbc'"
.CommandType = adCmdText
.Execute
End With
'IF this code is not called the INSERT bellow works fine, otherwise not--
--
Set rstRecordset = New ADODB.Recordset
rstRecordset.Open "SELECT * FROM TABLE1", cnnConn
'INSERT record on TABLE2--
With cmdCommand
.CommandText = "INSERT INTO TABLE2 (tipo, departamento, estado) VALUES
('A', 'XXX', 'P')"
.CommandType = adCmdText
.Execute
End With
..
ErrorHandler:
' clean up
End SubDid you turn pooling off in your connection string? If not, then
another connection is being opened under the covers and in that
connection the approle is not active. There's more information at
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q229564.
--Mary
On Tue, 05 Sep 2006 13:42:54 GMT, "Daniel Rodrigues" <u26179@.uwe>
wrote:
>Hi
>I'm using an application role to restrict acess to the users to a Database
>The Application role is always activated without any problem.
>Nevertheless I'm receiving permission error messages ("XXXX permission denied
>on object 'TABLE', database...") when inserting, updating or deleting
>records if I open a recordset before this operations, otherwise everything
>works fine.
>Below is an example of this,
>If I call the following code before I trie to INSERT a record in TABLE2 I
>receive the permission error message
>..
>Set rstRecordset = New ADODB.Recordset
>rstRecordset.Open "SELECT * FROM TABLE1", cnnConn
>..
>If I don't call the previous code the INSERT works ...
>(The AppRole have SELECT, INSET, UPDATE and DELETE permissions on TABLE1 and
>TABLE2)
>Do someone have any idea why this behavior ?
>I'll apreciate any help.
>Many Thanks
>Daniel
>EXAMPLE:
>--
>Private Sub CommandButton1_Click()
>On Error GoTo ErrorHandler
>Dim cnnConn As ADODB.Connection
>Dim rstRecordset As ADODB.Recordset
>Dim cmdCommand As ADODB.Command
>Set cnnConn = New ADODB.Connection
>With cnnConn
> .Open _
> "Provider=SQLOLEDB;Integrated Security=SSPI;" & _
> "Persist Security Info=False;" & _
> "Initial Catalog=DBx;Data Source=SERVERx"
>End With
>'The AppRole is activated without problems--
>Set cmdCommand = New ADODB.Command
>Set cmdCommand.ActiveConnection = cnnConn
>With cmdCommand
> .CommandText = "Exec sp_setapprole AppRole, { Encrypt N Password} ,
>'odbc'"
> .CommandType = adCmdText
> .Execute
>End With
>'IF this code is not called the INSERT bellow works fine, otherwise not--
>--
>Set rstRecordset = New ADODB.Recordset
>rstRecordset.Open "SELECT * FROM TABLE1", cnnConn
>'INSERT record on TABLE2--
>With cmdCommand
> .CommandText = "INSERT INTO TABLE2 (tipo, departamento, estado) VALUES
>('A', 'XXX', 'P')"
> .CommandType = adCmdText
> .Execute
>End With
>..
>ErrorHandler:
> ' clean up
>End Sub|||Hello Mary
Yes.
I used in connection string "OLE DB Services = -2" and I also tried with
"Pooling=â'False " (despite I think this is only for .Net and I'm tried with
VB and Delphi6 with the same results.
I already read the KB article you mentioned.
Is there any other way to deactivate pooling ?
The only way I found to solve the problem was with one connection for the
selects and another one only for INSERT, UPDATE and DELETE statments.
By the way, i did't mentioned in my previous mail, I'm using SQLServer 2K
with SP4 and acessing with ADO using Delphi6 aplications.
Thanks for your answer
Best regards
Daniel
Mary Chipman [MSFT] wrote:
>Did you turn pooling off in your connection string? If not, then
>another connection is being opened under the covers and in that
>connection the approle is not active. There's more information at
>http://support.microsoft.com/default.aspx?scid=kb;en-us;Q229564.
>--Mary
>>Hi
>[quoted text clipped - 68 lines]
>> ' clean up
>>End Sub
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200609/1