Re: Role activation
From: Mary Chipman (mchip@nomail.please)
Date: 03/30/03
- Next message: Kalen Delaney: "Re: What exactly can PUBLIC ROLE do in SqlServer?"
- Previous message: Erika Christensen [MSFT]: "RE: MDF file"
- In reply to: Darko Grujic: "Re: Role activation"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
From: Mary Chipman <mchip@nomail.please> Date: Sun, 30 Mar 2003 13:18:36 -0500
One way is to check to see if the database user name is the same as
the approle name -- "SELECT user" or "SELECT User_name()"
-- Mary
MCW Technologies
http://www.mcwtech.com
On Sat, 29 Mar 2003 17:30:00 -0800, "Darko Grujic"
<darkogrujic@hotmail.com> wrote:
>If I understand right I should be checking if the approle
>has been activated using the connection that attempted to
>activate that role. In other words if using for example
>the Excel VBA I open connection and then check the user
>using that connection. Any idea what that VBA query would
>look like.
>
>Darko
>
>>-----Original Message-----
>>The application role is connection-specific. What happens
>is that when
>>a connection is returned to the pool it can be reused
>when the
>>security credentials and string are identical. If one of
>these
>>connections has had an approle activated prior to being
>returned to
>>the pool, and then it gets reactivated, you get an error.
>On the other
>>hand, OLE DB can open additional connections under the
>covers, so you
>>may activate the approle on one of these connections, but
>another
>>connection has been opened that does not have the approle
>activated
>>(as appears to be the case). The connection pooling
>article I cirected
>>you to shows you how to turn off pooling in the
>connection string so
>>that connections don't get re-used from the pool. This is
>a bad idea
>>if your app needs to scale--however, if you don't have a
>lot of
>>concurrent users it can work out ok.
>>
>>-- Mary
>>MCW Technologies
>>http://www.mcwtech.com
>>
>>On Fri, 28 Mar 2003 23:20:52 -0800, "Darko Grujic"
>><darkogrujic@hotmail.com> wrote:
>>
>>>Thanks for your reply. Yes I'm quite positive that it
>>>doesn't activate:
>>>
>>>I do check it with SELECT USER from Query Analyzer. It
>>>returns approle in the first case and dbo in VBA case.
>>>I believe it doesn't matter where the SELECT USER is
>>>issued from. Or maybe it does, so it returns approle
>when
>>>SELECT USER is issued from there but doesn't get that
>info
>>>when Excel has tried the activation. I'll have to check.
>>>I'm not sure I understand connection pooling. Maybe
>that's
>>>the answer.
>>>
>>>Darko
>>>
>>>>-----Original Message-----
>>>>Are you sure? How are you verifying that it didn't get
>>>activated on
>>>>that connection? You can select the USER or USER_NAME
>to
>>>verify--it
>>>>should change to the role name. Bear in mind that
>>>approles are
>>>>connection-specific, and unless you turn off connection
>>>pooling in
>>>>your connection string, it might not be active. see
>this
>>>link for more
>>>>info:
>>>>http://support.microsoft.com/default.aspx?scid=KB;EN-
>>>US;Q229564
>>>>
>>>>-- Mary
>>>>MCW Technologies
>>>>http://www.mcwtech.com
>>>>
>>>>On Thu, 27 Mar 2003 03:59:53 -0800, "Darko Grujic"
>>>><darkogrujic@hotmail.com> wrote:
>>>>
>>>>>I can activate an application role using Query
>Analyzer
>>>>>with EXEC
>>>sp_setapprole "RoleName", "RolePassword"
>>>>>but not using VBA from within Excel or from within
>>>another
>>>>>application:
>>>>>
>>>>> Dim oConnection As ADODB.Connection
>>>>>' Create the connection object and connect.
>>>>>Set oConnection = CreateObject("ADODB.Connection")
>>>>>oConnection.Provider = "sqloledb"
>>>>>oConnection.Open "Server=SERVERNAME;Database=pubs;Trust
>ed
>>>_C
>>>>>onnection=yes"
>>>>>
>>>>>' Activate the application role. There is no error
>>>>>handling for this sample.
>>>>>oConnection.Execute "EXEC
>>>>>sp_setapprole 'AccAppRole', 'ABC'"
>>>>>
>>>>>This runs with no errors, all libraries exist, but the
>>>>>role does not get activated.
>>>>
>>>>.
>>>>
>>
>>.
>>
- Next message: Kalen Delaney: "Re: What exactly can PUBLIC ROLE do in SqlServer?"
- Previous message: Erika Christensen [MSFT]: "RE: MDF file"
- In reply to: Darko Grujic: "Re: Role activation"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
