Re: Problem with bulk load security.
- From: rbielaws@xxxxxxxxx
- Date: Mon, 2 Feb 2009 10:29:10 -0800 (PST)
On Jan 30, 6:06 pm, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
(rbiel...@xxxxxxxxx) writes:
I think that describes what I did.
I logged in with the ID that worked.
Then I defined the procedure using 'with execute as self'.
Which should be the same as 'with execute as <working ID>'
It's the only ID that seemed to have any hope of working.
As for user vs login, I don't see a syntax difference.
I only see this which indicates 'user_name' is my only option.
OK, sorry I forgot that you were using the EXECUTE AS clause in a
store procedure.
There is also a *statement* EXECUTE AS, and this statement has both a
user and a login option. For a stored procedure you only have the
option of impersonating a database user, which means that unless the
database is trustworthy, you cannot get rights outside the database. And
ADMINISTER BULK OPERATIONS is a server-level permission.
I can see two ways to go.
One is to make the database trustworthy, ALTER DATABASE db SET TRUSTWORTHY
ON. As I recall, this should be enough. Now, making a datbase trustworhty
has some consequences that may be perfectly acceptable or entirely
unacceptable depending on the situation. If you are the DBA, and there
is no other person who have db_owner rights in this database without
also having sysadmin rights, making the database trustworthy is not a
big deal. But if there are persons who have privileges within this database,
but who not should be permitted to fiddle on server level, making the
database trustworthy is not adviseable.
The other option is a little more complicated. You remove the
EXCECUTE AS clause on procedure level. Instead you add EXECUTE AS LOGIN
before the BULK INSERT command and REVERT after it. If the Windows
login that runs the procedure is a privileged account this is enough.
Else you need to create a certificate in master, and create a login
from that certificate, and grant that login IMPERSONATE permission on
the Windows user for the service account. Then you need import the
certificate into the database and sign the procedure with the certificate..
Now, if that went over your head, don't be alarmed. I have an article
on my web site that describes the certificate business in more detail.http://www.sommarskog.se/grantperm.html.
--
Erland Sommarskog, SQL Server MVP, esq...@xxxxxxxxxxxxx
Links for SQL Server Books Online:
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Thanks for pointing me to that 'trustworthy' setting.
Unfortunately it only changed the error I'm getting.
It fixed the error saying I don't have bulk load access but didn't
provide access. It just switched to the error saying the file can't
be opened. On the app server I see the SQL Server trying to get in as
anonymous -- similar to when I use Windows authentication.
So it seems that setting a function to 'execute as' some other user
(even with the trustworthy option on) doesn't give that function
entirely the same abilities as executing the function while actually
logged on as that same user.
It seems our only choice is to hard-code passwords to an SQL Server
account. Very dissappointing.
.
- Follow-Ups:
- Re: Problem with bulk load security.
- From: Erland Sommarskog
- Re: Problem with bulk load security.
- Prev by Date: Re: Login of some items of an AD Group fails
- Next by Date: Re: Problem with bulk load security.
- Previous by thread: Login of some items of an AD Group fails
- Next by thread: Re: Problem with bulk load security.
- Index(es):
Relevant Pages
|
