Caching with Dynamic Security

I defined one role in my AS database with dynamic security for each dimension. I am accessing the AS database in an ASP.NET web application which runs under a domain user and uses Form Authentication. Therefore I always connect to the AS database under the same domain user even though security should be based on the user logged into the web application. When I run a MDX query, I pass along the web user's security info as part of the connectionstring and uses dynamic security to get the AllowedSet for each dimension. However, I notice my user defined function is ran only on the first time I run a query, meaning the dimension security is cached based on the domain user in the connection string and not the web user. Sorry if this sounds confusing but I can clarify a bit more if needed. My question boils down to: Is there a way to tell AS database to cache result base on the CustomData property of the connectionstring?

The answer is YES - AS is smart enough to recognize that different values of CustomData were used even though the real identity on the connection is the same. Since the main purpose of CustomData was for custom authentication - it is treated the same as different users. Same is true w.r.t. Roles and UserId properties.

HTH,

Mosha (http://www.mosha.com/msolap)

|||

Mosha,

As always, you are right and thanks for the help. I did a little more testing after my post and realized AS is caching the result base on the CustomData property. Thanks!

Bulkadmin role (BULK INSERT)

Hello,

I am trying to load a simple tab-delimited data file to SQL Server. I
created a format file to go with it, since the data file differs from
the destination table in number of columns.

When I execute the query, I get an error saying that only sysadmin or
bulkadmin roles are allowed to use the BULK INSERT statement. So, I
proceeded with the Enterprise Manager to grant myself those roles.
However, I could not find sysadmin or bulkadmin roles using the
Enterprise Manager. From what I read from my books, I thought these
were fixed server roles and that they would be there.

So I have a few questions:
1) How do I create a user account/role that can issue BULK INSERT
commands?

2) Why is BULK INSERT considered a dangerous operation that it
requires special privileges? What are its implications? I have a
couple of books that say that a user should be aware of its
implications before using it, but they don't actually describe what
those implications might be.

3) It seems that I can load the data file using BCP utility, without
such privileges. If so, what is the difference?

Thanks!> So, I proceeded with the Enterprise Manager to grant myself those
> roles. However, I could not find sysadmin or bulkadmin roles using the
> Enterprise Manager. From what I read from my books, I thought these
> were fixed server roles and that they would be there.
> So I have a few questions:
> 1) How do I create a user account/role that can issue BULK INSERT
> commands?

The roles are there but you need to be a sysadmin role member or a member of
that fixed server role in order to add members. Ask your DBA to do this.

> 2) Why is BULK INSERT considered a dangerous operation that it
> requires special privileges? What are its implications? I have a
> couple of books that say that a user should be aware of its
> implications before using it, but they don't actually describe what
> those implications might be.

The main security implication is that BULK INSERT accesses external data
under the security context of the SQL Server service account rather than the
invoking user's account.

> 3) It seems that I can load the data file using BCP utility, without
> such privileges. If so, what is the difference?

Client-based bulk insert techniques like SQLOLEDB IRowsetFastLoad and ODBC
BCP access data under the security context of the invoking user.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"php newbie" <newtophp2000@.yahoo.com> wrote in message
news:124f428e.0406052020.16b6b4e6@.posting.google.c om...
> Hello,
> I am trying to load a simple tab-delimited data file to SQL Server. I
> created a format file to go with it, since the data file differs from
> the destination table in number of columns.
> When I execute the query, I get an error saying that only sysadmin or
> bulkadmin roles are allowed to use the BULK INSERT statement. So, I
> proceeded with the Enterprise Manager to grant myself those roles.
> However, I could not find sysadmin or bulkadmin roles using the
> Enterprise Manager. From what I read from my books, I thought these
> were fixed server roles and that they would be there.
> So I have a few questions:
> 1) How do I create a user account/role that can issue BULK INSERT
> commands?
> 2) Why is BULK INSERT considered a dangerous operation that it
> requires special privileges? What are its implications? I have a
> couple of books that say that a user should be aware of its
> implications before using it, but they don't actually describe what
> those implications might be.
> 3) It seems that I can load the data file using BCP utility, without
> such privileges. If so, what is the difference?
> Thanks!|||"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<bRGwc.3644$uX2.3489@.newsread2.news.pas.earthlink.n et>...
> > So, I proceeded with the Enterprise Manager to grant myself those
> > roles. However, I could not find sysadmin or bulkadmin roles using the
> > Enterprise Manager. From what I read from my books, I thought these
> > were fixed server roles and that they would be there.
> > So I have a few questions:
> > 1) How do I create a user account/role that can issue BULK INSERT
> > commands?
> The roles are there but you need to be a sysadmin role member or a member of
> that fixed server role in order to add members. Ask your DBA to do this.

Hello Dan,

This was for personal use, so that makes me the DBA. I believe I
disabled the "sa" account when I first installed SQL Server (based on
some suggestions due to security risks). Perhaps that has something
to do with it. I will look into it.

> Client-based bulk insert techniques like SQLOLEDB IRowsetFastLoad and ODBC
> BCP access data under the security context of the invoking user.

Thanks! This clarifies the risk implications of BULK INSERT vs. bcp
that was not in the books. It looks like Bcp is the sure way to go
for most users.

> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP