Re: Problem with bulk load security.

(rbielaws@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, esquel@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

.

Relevant Pages

  • RE: Mystery SQL database access
    ... It sounds like mapping issue on the server. ... MCITP: Database Administrator ... I see a standard SQL Server login. ... No permission to execute sps has been granted to public so even if the ...
    (microsoft.public.sqlserver.security)
  • Re: how to remove builtinadministrators
    ... Perhaps you have to change the owner to a new login. ... the new owner is known as the dbo user inside the database. ... To display a list of the valid login values, execute the sp_helplogins stored procedure. ...
    (microsoft.public.sqlserver.programming)
  • Re: Adding Permissions
    ... every database and grant it rights to execute all stored procedures. ... Director of Text Mining and Database Strategy ... not using sa as the login. ...
    (microsoft.public.sqlserver.security)
  • Re: DB name changes daily
    ... then go back to where it was before (Default datbase context). ... and then go back to default database context. ... exec (USE northwind execute ) ... Have a common login ...
    (microsoft.public.sqlserver.mseq)
  • Re: PDO: Switch database user without reopening connection
    ... At the bare minimum there will be a login user who only has ... modifications to the database as well (editors get update permission, ... As database connections are expensive to ... a certain visitor in the Session, and use that value to start the right ...
    (comp.lang.php)