|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Bulk Insert To Temp Table - Security IssueI have a stored procedure that dynamically builds a BULK INSERT command and
load data into a temp table. I can run it fine. Of course I am the dbo and sa. The user has BULK Administration rites setup in SQL. I gave the user dbo access to the database because the BUILk Admin still did not let them run the BULK command in the sp. Now that they are BULK Admin and dbo, they can run the sp but it gives a security error using the temp table in the BULK INSERT code. When I change the BULK INSERT code to load into a real table, it works perfectly. The user has dbo access only to the database that the sp is running against. I would rather take the code back to a temp table. What is security setup is missing? Thanks MG Why can't you grant access to tempdb? Do you store encryption algorithms,
credit card information, SSNs in permanent tables there? Otherwise, all they will be able to access is the #table they create. Show quoteHide quote "MG" <M*@discussions.microsoft.com> wrote in message news:BDBD991D-23CD-4F39-ACF8-EB10CBEE830B@microsoft.com... >I have a stored procedure that dynamically builds a BULK INSERT command and > load data into a temp table. I can run it fine. Of course I am the dbo and > sa. > > The user has BULK Administration rites setup in SQL. > I gave the user dbo access to the database because the BUILk Admin still > did > not let them run the BULK command in the sp. Now that they are BULK Admin > and > dbo, they can run the sp but it gives a security error using the temp > table > in the BULK INSERT code. When I change the BULK INSERT code to load into a > real table, it works perfectly. The user has dbo access only to the > database > that the sp is running against. I would rather take the code back to a > temp > table. > > What is security setup is missing? > > Thanks MG Aaron,
Forget to document in the original posting that I tried giving them access to TEMPDB. It did not make a difference. Thus, they had BULK priviledges, dbo priviledges, and permission in TEMPDB (which I do disagree that they would need special priviledge for TEMPDB since all of the sps use it anyway) - it made no differences. Show quoteHide quote "Aaron Bertrand [SQL Server MVP]" wrote: > Why can't you grant access to tempdb? Do you store encryption algorithms, > credit card information, SSNs in permanent tables there? Otherwise, all > they will be able to access is the #table they create. > > > > > "MG" <M*@discussions.microsoft.com> wrote in message > news:BDBD991D-23CD-4F39-ACF8-EB10CBEE830B@microsoft.com... > >I have a stored procedure that dynamically builds a BULK INSERT command and > > load data into a temp table. I can run it fine. Of course I am the dbo and > > sa. > > > > The user has BULK Administration rites setup in SQL. > > I gave the user dbo access to the database because the BUILk Admin still > > did > > not let them run the BULK command in the sp. Now that they are BULK Admin > > and > > dbo, they can run the sp but it gives a security error using the temp > > table > > in the BULK INSERT code. When I change the BULK INSERT code to load into a > > real table, it works perfectly. The user has dbo access only to the > > database > > that the sp is running against. I would rather take the code back to a > > temp > > table. > > > > What is security setup is missing? > > > > Thanks MG > > > MG (M*@discussions.microsoft.com) writes:
> I have a stored procedure that dynamically builds a BULK INSERT command The quirk is that the tempdb has dbo as owner, not the user, which> and load data into a temp table. I can run it fine. Of course I am the > dbo and sa. > > The user has BULK Administration rites setup in SQL. > I gave the user dbo access to the database because the BUILk Admin still > did not let them run the BULK command in the sp. Now that they are BULK > Admin and dbo, they can run the sp but it gives a security error using > the temp table in the BULK INSERT code. When I change the BULK INSERT > code to load into a real table, it works perfectly. The user has dbo > access only to the database that the sp is running against. I would > rather take the code back to a temp table. apparently matters in this case. See this old thread for some more details: http://groups.google.se/group/comp.databases.ms-sqlserver/browse_thread/thread/8b7dff51b69819fe/9174fb932bed4123?lnk=st&q=&rnum=1&hl=sv#9174fb932bed4123 I take it that you are on SQL 2000? -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Erland,
Thanks so much for you help. I thought I had tried that combination of dbo for tempdb yesterday but must have missed it. Once I made the user DBO for tempdb, which alone seems preposterous, BULK INSERT works with a temp table. This is a mess. I truley believe that MS missed this one on their design - they should have setup SQL to have BULK Admin permission at either the SQL level or database level. Then not required someone to be DBO - it is ludicrous. Regardless, it looks like that is what I will be doing: User is DBO of the database. User is DBO of TEMPDB. User is BULK Admin It is rather funny in that I had designed the security wherey there is a Role called SuperUser which the few users are members of. Now they are DBO, which makes SuperUser pretty much worthless but I will keep it and keep them in it just the same incase we get ride of BULK INSERT in a sp. All this because of the BULK INSERT design - someday I will laugh but not now. One more question for you, I have never given anyone dbo for TEMPDb. Are there any drawbacks? Thanks again, MG Show quoteHide quote "Erland Sommarskog" wrote: > MG (M*@discussions.microsoft.com) writes: > > I have a stored procedure that dynamically builds a BULK INSERT command > > and load data into a temp table. I can run it fine. Of course I am the > > dbo and sa. > > > > The user has BULK Administration rites setup in SQL. > > I gave the user dbo access to the database because the BUILk Admin still > > did not let them run the BULK command in the sp. Now that they are BULK > > Admin and dbo, they can run the sp but it gives a security error using > > the temp table in the BULK INSERT code. When I change the BULK INSERT > > code to load into a real table, it works perfectly. The user has dbo > > access only to the database that the sp is running against. I would > > rather take the code back to a temp table. > > The quirk is that the tempdb has dbo as owner, not the user, which > apparently matters in this case. > > See this old thread for some more details: > http://groups.google.se/group/comp.databases.ms-sqlserver/browse_thread/thread/8b7dff51b69819fe/9174fb932bed4123?lnk=st&q=&rnum=1&hl=sv#9174fb932bed4123 > > I take it that you are on SQL 2000? > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > Just a comment on your original design; might it simplify things to use a
table variable instead of a temp table? -- Show quoteHide quoteglen "MG" wrote: > Erland, > > Thanks so much for you help. I thought I had tried that combination of dbo > for tempdb yesterday but must have missed it. Once I made the user DBO for > tempdb, which alone seems preposterous, BULK INSERT works with a temp table. > This is a mess. I truley believe that MS missed this one on their design - > they should have setup SQL to have BULK Admin permission at either the SQL > level or database level. Then not required someone to be DBO - it is > ludicrous. Regardless, it looks like that is what I will be doing: > > User is DBO of the database. > User is DBO of TEMPDB. > User is BULK Admin > > It is rather funny in that I had designed the security wherey there is a > Role called SuperUser which the few users are members of. Now they are DBO, > which makes SuperUser pretty much worthless but I will keep it and keep them > in it just the same incase we get ride of BULK INSERT in a sp. All this > because of the BULK INSERT design - someday I will laugh but not now. > > One more question for you, I have never given anyone dbo for TEMPDb. Are > there any drawbacks? > Thanks again, > > MG > > > > "Erland Sommarskog" wrote: > > > MG (M*@discussions.microsoft.com) writes: > > > I have a stored procedure that dynamically builds a BULK INSERT command > > > and load data into a temp table. I can run it fine. Of course I am the > > > dbo and sa. > > > > > > The user has BULK Administration rites setup in SQL. > > > I gave the user dbo access to the database because the BUILk Admin still > > > did not let them run the BULK command in the sp. Now that they are BULK > > > Admin and dbo, they can run the sp but it gives a security error using > > > the temp table in the BULK INSERT code. When I change the BULK INSERT > > > code to load into a real table, it works perfectly. The user has dbo > > > access only to the database that the sp is running against. I would > > > rather take the code back to a temp table. > > > > The quirk is that the tempdb has dbo as owner, not the user, which > > apparently matters in this case. > > > > See this old thread for some more details: > > http://groups.google.se/group/comp.databases.ms-sqlserver/browse_thread/thread/8b7dff51b69819fe/9174fb932bed4123?lnk=st&q=&rnum=1&hl=sv#9174fb932bed4123 > > > > I take it that you are on SQL 2000? > > > > -- > > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > > > Books Online for SQL Server 2005 at > > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > > Books Online for SQL Server 2000 at > > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > Glen,
Thanks for your input but I do have a few questions. I have not used table variables yet. I guess the ultimate question is how does SQL handle the storage of the table variable - if it uses tempdb, I am betting that I will still need permissions for the user in tempdb like I have had to do. I could not find anything in BOL as to what SQL does with the table variable when it is created. Let me know if you have an answer. Thanks, Mark Show quoteHide quote "glen" wrote: > Just a comment on your original design; might it simplify things to use a > table variable instead of a temp table? > -- > glen > > > "MG" wrote: > > > Erland, > > > > Thanks so much for you help. I thought I had tried that combination of dbo > > for tempdb yesterday but must have missed it. Once I made the user DBO for > > tempdb, which alone seems preposterous, BULK INSERT works with a temp table. > > This is a mess. I truley believe that MS missed this one on their design - > > they should have setup SQL to have BULK Admin permission at either the SQL > > level or database level. Then not required someone to be DBO - it is > > ludicrous. Regardless, it looks like that is what I will be doing: > > > > User is DBO of the database. > > User is DBO of TEMPDB. > > User is BULK Admin > > > > It is rather funny in that I had designed the security wherey there is a > > Role called SuperUser which the few users are members of. Now they are DBO, > > which makes SuperUser pretty much worthless but I will keep it and keep them > > in it just the same incase we get ride of BULK INSERT in a sp. All this > > because of the BULK INSERT design - someday I will laugh but not now. > > > > One more question for you, I have never given anyone dbo for TEMPDb. Are > > there any drawbacks? > > Thanks again, > > > > MG > > > > > > > > "Erland Sommarskog" wrote: > > > > > MG (M*@discussions.microsoft.com) writes: > > > > I have a stored procedure that dynamically builds a BULK INSERT command > > > > and load data into a temp table. I can run it fine. Of course I am the > > > > dbo and sa. > > > > > > > > The user has BULK Administration rites setup in SQL. > > > > I gave the user dbo access to the database because the BUILk Admin still > > > > did not let them run the BULK command in the sp. Now that they are BULK > > > > Admin and dbo, they can run the sp but it gives a security error using > > > > the temp table in the BULK INSERT code. When I change the BULK INSERT > > > > code to load into a real table, it works perfectly. The user has dbo > > > > access only to the database that the sp is running against. I would > > > > rather take the code back to a temp table. > > > > > > The quirk is that the tempdb has dbo as owner, not the user, which > > > apparently matters in this case. > > > > > > See this old thread for some more details: > > > http://groups.google.se/group/comp.databases.ms-sqlserver/browse_thread/thread/8b7dff51b69819fe/9174fb932bed4123?lnk=st&q=&rnum=1&hl=sv#9174fb932bed4123 > > > > > > I take it that you are on SQL 2000? > > > > > > -- > > > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > > > > > Books Online for SQL Server 2005 at > > > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > > > Books Online for SQL Server 2000 at > > > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > > Glen
Thanks for the help but it will not work. I redesigned the sp to use a Table variable as the recipient in the BULK INSERT statement and SQL absolutely does not like it. It indicates 'incorrect syntax' at the bulk insert line. Thus if I have created a table variable like this: Declare @mystuff table ( Field1 char(10) NULL, Field2 char(10) NULL ) 'BULK INSERT @mystuff FROM ......' It appears that it does not like using the table variable as the recipient - sytax error. Maybe it is one of those undocumented "can't do it with a table variable" items. BULK INSERT is it's own kind of beast. Thanks anyway. Mark Show quoteHide quote "glen" wrote: > Just a comment on your original design; might it simplify things to use a > table variable instead of a temp table? > -- > glen > > > "MG" wrote: > > > Erland, > > > > Thanks so much for you help. I thought I had tried that combination of dbo > > for tempdb yesterday but must have missed it. Once I made the user DBO for > > tempdb, which alone seems preposterous, BULK INSERT works with a temp table. > > This is a mess. I truley believe that MS missed this one on their design - > > they should have setup SQL to have BULK Admin permission at either the SQL > > level or database level. Then not required someone to be DBO - it is > > ludicrous. Regardless, it looks like that is what I will be doing: > > > > User is DBO of the database. > > User is DBO of TEMPDB. > > User is BULK Admin > > > > It is rather funny in that I had designed the security wherey there is a > > Role called SuperUser which the few users are members of. Now they are DBO, > > which makes SuperUser pretty much worthless but I will keep it and keep them > > in it just the same incase we get ride of BULK INSERT in a sp. All this > > because of the BULK INSERT design - someday I will laugh but not now. > > > > One more question for you, I have never given anyone dbo for TEMPDb. Are > > there any drawbacks? > > Thanks again, > > > > MG > > > > > > > > "Erland Sommarskog" wrote: > > > > > MG (M*@discussions.microsoft.com) writes: > > > > I have a stored procedure that dynamically builds a BULK INSERT command > > > > and load data into a temp table. I can run it fine. Of course I am the > > > > dbo and sa. > > > > > > > > The user has BULK Administration rites setup in SQL. > > > > I gave the user dbo access to the database because the BUILk Admin still > > > > did not let them run the BULK command in the sp. Now that they are BULK > > > > Admin and dbo, they can run the sp but it gives a security error using > > > > the temp table in the BULK INSERT code. When I change the BULK INSERT > > > > code to load into a real table, it works perfectly. The user has dbo > > > > access only to the database that the sp is running against. I would > > > > rather take the code back to a temp table. > > > > > > The quirk is that the tempdb has dbo as owner, not the user, which > > > apparently matters in this case. > > > > > > See this old thread for some more details: > > > http://groups.google.se/group/comp.databases.ms-sqlserver/browse_thread/thread/8b7dff51b69819fe/9174fb932bed4123?lnk=st&q=&rnum=1&hl=sv#9174fb932bed4123 > > > > > > I take it that you are on SQL 2000? > > > > > > -- > > > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > > > > > Books Online for SQL Server 2005 at > > > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > > > Books Online for SQL Server 2000 at > > > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > > MG (M*@discussions.microsoft.com) writes:
> Thanks so much for you help. I thought I had tried that combination of Yes, it's apparently a glitch. In SQL 2005 I was table to import > dbo for tempdb yesterday but must have missed it. Once I made the user > DBO for tempdb, which alone seems preposterous, BULK INSERT works with a > temp > table. This is a mess. I truley believe that MS missed this one > on their design - they should have setup SQL to have BULK Admin > permission at either the SQL level or database level. into temp table with a plain user without an extra tricks´ but granting ADMINISTER BULK OPERATIONS to the login. Pesonally, I would not add users as dbo in tempdb. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx I had this issue and created a separate database StagingDB where
everybody is a dbo. Since then BULK INSERTs work all right as long as the target table in in StagingDB. Alexander,
Thanks for the option. That is one that I may decide to use down the road. As you may note in my previous reply to Glen, the use of a table variable does not work either with BULK INSERT. It does not like the @aaaaa in the recipient field - I could not find a way of getting the table variable to work. Thanks, MG Show quoteHide quote "Alexander Kuznetsov" wrote: > I had this issue and created a separate database StagingDB where > everybody is a dbo. > Since then BULK INSERTs work all right as long as the target table in > in StagingDB. > > 
Other interesting topics
Deleting a record dynamically
What am I missing with this simple query? Passing username to sql server from an app SQL search for similar records with different dates Different result from Script and SP UPDATE TOP 1 For insert trigger problem Is it possible to trace all statements to a specific table?... Any need for TEXT/NTEXT column types in SQL2005 Joined Query With Optional Data |
|||||||||||||||||||||||
