bcp permissions for non SA

A few years ago I had to enable users to use bcp. I recall that I had to go
in and set the proxy account via the Job System tab of the SQL Server Agent.
I thought that was ALL I had to do. But now it appears as though I need to
grant them exec rights on xp_cmdshell itself? That sucks for obvious reasons.
Is there a way around it? I really dont recall having to do that, but as
mentioned it was years ago.
TIA,
ChrisR
Chris,
Try BULK INSERT.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:A86E8074-8781-4EF2-BA84-D566C7663BB4@.microsoft.com...
>A few years ago I had to enable users to use bcp. I recall that I had to go
> in and set the proxy account via the Job System tab of the SQL Server
> Agent.
> I thought that was ALL I had to do. But now it appears as though I need to
> grant them exec rights on xp_cmdshell itself? That sucks for obvious
> reasons.
> Is there a way around it? I really dont recall having to do that, but as
> mentioned it was years ago.
> --
> TIA,
> ChrisR
sql

bcp permissions for non SA

A few years ago I had to enable users to use bcp. I recall that I had to go
in and set the proxy account via the Job System tab of the SQL Server Agent.
I thought that was ALL I had to do. But now it appears as though I need to
grant them exec rights on xp_cmdshell itself? That sucks for obvious reasons.
Is there a way around it? I really dont recall having to do that, but as
mentioned it was years ago.
--
TIA,
ChrisRChris,
Try BULK INSERT.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:A86E8074-8781-4EF2-BA84-D566C7663BB4@.microsoft.com...
>A few years ago I had to enable users to use bcp. I recall that I had to go
> in and set the proxy account via the Job System tab of the SQL Server
> Agent.
> I thought that was ALL I had to do. But now it appears as though I need to
> grant them exec rights on xp_cmdshell itself? That sucks for obvious
> reasons.
> Is there a way around it? I really dont recall having to do that, but as
> mentioned it was years ago.
> --
> TIA,
> ChrisR

bcp permissions for non SA

A few years ago I had to enable users to use bcp. I recall that I had to go
in and set the proxy account via the Job System tab of the SQL Server Agent.
I thought that was ALL I had to do. But now it appears as though I need to
grant them exec rights on xp_cmdshell itself? That sucks for obvious reasons
.
Is there a way around it? I really dont recall having to do that, but as
mentioned it was years ago.
--
TIA,
ChrisRChris,
Try BULK INSERT.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:A86E8074-8781-4EF2-BA84-D566C7663BB4@.microsoft.com...
>A few years ago I had to enable users to use bcp. I recall that I had to go
> in and set the proxy account via the Job System tab of the SQL Server
> Agent.
> I thought that was ALL I had to do. But now it appears as though I need to
> grant them exec rights on xp_cmdshell itself? That sucks for obvious
> reasons.
> Is there a way around it? I really dont recall having to do that, but as
> mentioned it was years ago.
> --
> TIA,
> ChrisR

BCP

1. When using BCP to copy data into a table, which of the following
statements apply? Choose 2.
A) Database users will not be able to access the table because BCP
will lock it.
B) Database users will see the rows inserted by BCP after each batch
is complete.
C) You must have INSERT permissions on the table.
D) Existing rows are replaced by BCP.> 1. When using BCP to copy data into a table, which of the following
> statements apply? Choose 2.
> A) Database users will not be able to access the table because BCP
> will lock it.
>
The default behavior is row locking so other users can use the table.
However, a TABLOCK hint can also be specified.
> B) Database users will see the rows inserted by BCP after each batch
> is complete.
>
A BCP batch is a transaction so users can see data once committed.
> C) You must have INSERT permissions on the table.
>
Yes.
> D) Existing rows are replaced by BCP.
>
Nope.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"docsql" <docsql@.noemail.nospam> wrote in message
news:eP8VbIl2FHA.3272@.TK2MSFTNGP09.phx.gbl...|||Not sure if this question related to SQL2000 or SQL2005? Some more
information on permissions for BCP in SQL2005. You will note there is now a
requirement to get ALTER table permission if you are doing DDL operations
transparently. This was not the case in SQL2000. However, you will also
require SELECT permission on the traget table both in SQL2000 and SQL2005
A bcp out operation requires SELECT permission on the source table.
A bcp in operation minimally requires SELECT/INSERT permissions on the
target table. In addition, ALTER TABLE permission is required if any of the
following is true:
(1)Constraints are disabled, which is the default behavior. To keep
constraints enabled, use the -h option with the CHECK_CONSTRAINTS hint.
(2) Triggers are disabled, which is the default behavior. To fire triggers,
use the -h option with the FIRE_TRIGGERS hint.
(3) You use the -E option to import identity values from a data file.
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:elgg6Cp2FHA.3592@.TK2MSFTNGP12.phx.gbl...
>> 1. When using BCP to copy data into a table, which of the following
>> statements apply? Choose 2.
>> A) Database users will not be able to access the table because BCP
>> will lock it.
> The default behavior is row locking so other users can use the table.
> However, a TABLOCK hint can also be specified.
>> B) Database users will see the rows inserted by BCP after each
>> batch is complete.
> A BCP batch is a transaction so users can see data once committed.
>
>> C) You must have INSERT permissions on the table.
> Yes.
>> D) Existing rows are replaced by BCP.
>>
> Nope.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:eP8VbIl2FHA.3272@.TK2MSFTNGP09.phx.gbl...
>

BCP

1. When using BCP to copy data into a table, which of the following
statements apply? Choose 2.
A) Database users will not be able to access the table because BCP
will lock it.
B) Database users will see the rows inserted by BCP after each batch
is complete.
C) You must have INSERT permissions on the table.
D) Existing rows are replaced by BCP.
> 1. When using BCP to copy data into a table, which of the following
> statements apply? Choose 2.
> A) Database users will not be able to access the table because BCP
> will lock it.
>
The default behavior is row locking so other users can use the table.
However, a TABLOCK hint can also be specified.

> B) Database users will see the rows inserted by BCP after each batch
> is complete.
>
A BCP batch is a transaction so users can see data once committed.

> C) You must have INSERT permissions on the table.
>
Yes.

> D) Existing rows are replaced by BCP.
>
Nope.
Hope this helps.
Dan Guzman
SQL Server MVP
"docsql" <docsql@.noemail.nospam> wrote in message
news:eP8VbIl2FHA.3272@.TK2MSFTNGP09.phx.gbl...
|||Not sure if this question related to SQL2000 or SQL2005? Some more
information on permissions for BCP in SQL2005. You will note there is now a
requirement to get ALTER table permission if you are doing DDL operations
transparently. This was not the case in SQL2000. However, you will also
require SELECT permission on the traget table both in SQL2000 and SQL2005
A bcp out operation requires SELECT permission on the source table.
A bcp in operation minimally requires SELECT/INSERT permissions on the
target table. In addition, ALTER TABLE permission is required if any of the
following is true:
(1)Constraints are disabled, which is the default behavior. To keep
constraints enabled, use the -h option with the CHECK_CONSTRAINTS hint.
(2) Triggers are disabled, which is the default behavior. To fire triggers,
use the -h option with the FIRE_TRIGGERS hint.
(3) You use the -E option to import identity values from a data file.
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:elgg6Cp2FHA.3592@.TK2MSFTNGP12.phx.gbl...
> The default behavior is row locking so other users can use the table.
> However, a TABLOCK hint can also be specified.
>
> A BCP batch is a transaction so users can see data once committed.
>
> Yes.
>
> Nope.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:eP8VbIl2FHA.3272@.TK2MSFTNGP09.phx.gbl...
>

BCP

1. When using BCP to copy data into a table, which of the following
statements apply? Choose 2.
A) Database users will not be able to access the table because BCP
will lock it.
B) Database users will see the rows inserted by BCP after each batch
is complete.
C) You must have INSERT permissions on the table.
D) Existing rows are replaced by BCP.> 1. When using BCP to copy data into a table, which of the following
> statements apply? Choose 2.
> A) Database users will not be able to access the table because BCP
> will lock it.
>
The default behavior is row locking so other users can use the table.
However, a TABLOCK hint can also be specified.

> B) Database users will see the rows inserted by BCP after each batch
> is complete.
>
A BCP batch is a transaction so users can see data once committed.

> C) You must have INSERT permissions on the table.
>
Yes.

> D) Existing rows are replaced by BCP.
>
Nope.
Hope this helps.
Dan Guzman
SQL Server MVP
"docsql" <docsql@.noemail.nospam> wrote in message
news:eP8VbIl2FHA.3272@.TK2MSFTNGP09.phx.gbl...|||Not sure if this question related to SQL2000 or SQL2005? Some more
information on permissions for BCP in SQL2005. You will note there is now a
requirement to get ALTER table permission if you are doing DDL operations
transparently. This was not the case in SQL2000. However, you will also
require SELECT permission on the traget table both in SQL2000 and SQL2005
A bcp out operation requires SELECT permission on the source table.
A bcp in operation minimally requires SELECT/INSERT permissions on the
target table. In addition, ALTER TABLE permission is required if any of the
following is true:
(1)Constraints are disabled, which is the default behavior. To keep
constraints enabled, use the -h option with the CHECK_CONSTRAINTS hint.
(2) Triggers are disabled, which is the default behavior. To fire triggers,
use the -h option with the FIRE_TRIGGERS hint.
(3) You use the -E option to import identity values from a data file.
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:elgg6Cp2FHA.3592@.TK2MSFTNGP12.phx.gbl...
> The default behavior is row locking so other users can use the table.
> However, a TABLOCK hint can also be specified.
>
> A BCP batch is a transaction so users can see data once committed.
>
> Yes.
>
> Nope.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:eP8VbIl2FHA.3272@.TK2MSFTNGP09.phx.gbl...
>

batch update in stored procedure

The following code is a part of my stored procedure MySP. I would like to update Users table with input variable @.userIDs which has the following format:

101, 102, 103

I got the error message:

Syntax error converting the varchar value '101, 102, 103' to a column of data type int.

Obviously, UserID has datatype int. How can I write this SP?

CREATE PROCEDURE dbo.MySP
@.userIDs varchar(100)
AS
SET NOCOUNT ON

BEGIN TRANSACTION

UPDATE Users SET IsActive = 1 WHERE UserID IN (@.userIDs)

IF @.@.ERROR <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

SET NOCOUNT OFFOriginally posted by gyuan
Obviously, UserID has datatype int.

Obviousley it doesn't...

Check this out:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=udf,csv,string

Use the UDF that's posted in there|||You could use dynamic query - sp_executesql or execute it.|||Originally posted by Brett Kaiser
Use the UDF that's posted in there

Brett,

I read the posters from the link you posted but I'm not very clear how to use UDF on my stored procedure since UDF is new to me. Can you give me some hint? UserID is the primary key in the table Users and its datatype is int. Thanks.|||Cut and paste the following code in to QA and run it...

USE Northwind
GO

CREATE FUNCTION CSVTable(@.Str varchar(7000))
RETURNS @.t table (numberval int, stringval varchar(100), DateVal datetime)
AS
BEGIN
DECLARE @.i int, @.c varchar(100);
SELECT @.Str = @.Str + ',', @.i = 1, @.c = '';
WHILE @.i <= LEN(@.Str)
BEGIN
IF substring(@.Str,@.i,1) = ','
BEGIN
INSERT INTO @.t(numberval, stringval, DateVal)
VALUES ( CASE WHEN ISNUMERIC(@.c)=1 THEN @.c ELSE Null END
, RTRIM(LTRIM(@.c))
, CASE WHEN ISDATE(@.c)=1 THEN @.c ELSE Null END)
SET @.c = ''
END
ELSE

SET @.c = @.c + substring(@.Str,@.i,1)
SET @.i = @.i +1
END
RETURN
END
GO

CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 int)
GO

INSERT INTO myTable99(Col2)
SELECT 100 UNION ALL SELECT 101 UNION ALL SELECT 102 UNION SELECT 103 UNION ALL
SELECT 104 UNION ALL SELECT 105 UNION ALL SELECT 106 UNION SELECT 107 UNION ALL
SELECT 108 UNION ALL SELECT 109 UNION ALL SELECT 110
GO

DECLARE @.userIDs varchar(100)
SELECT @.userIDs = '101,102,103'

SELECT *
FROM myTable99
WHERE Col2 IN (SELECT StringVal FROM dbo.CSVTable(@.userIDs))
GO

DROP FUNCTION CSVTable
DROP TABLE myTable99
GO

Thank you Dr. Cross Join

http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=6859|||I run it and get the result I need. So now do I need to add the function to the stored procedure?|||Just make you're WHERE Clause look like mine...

Just need to make sure the udf is in the same enviroment where you release the procedure...

Get it?|||Yes, it works. That is a great function. Thank you, Brett.

By the way, can you tell me what the difference is between UDF and dynamic SQL? Performance?|||Yeah, go to the link and give thanks to Jeff..

Use dynamic sql as a last resort...and never in application code...

I use it for admin support...but it's not released...

Basic Windows Authentication Question

Hi there,
I want to give access to a database to all users in a domain
using Windows Authentication. I thought that this would be as simple as
adding a login which specifies the domain only, but this does not seem to be
possible. I can't believe that you have to enter every single domain user
as a login in SQL Server. Someone please tell me this is not the case!Hi,
Not required, Create OS Group and all the domain users to that group. After
that give the previlages to
that domain group to SQL Server.
Thanks
Hari
MCDBA
"Gorge Bush" <bill.gates@.microcock.cum> wrote in message
news:#FwxRFFVEHA.2320@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> I want to give access to a database to all users in a
domain
> using Windows Authentication. I thought that this would be as simple as
> adding a login which specifies the domain only, but this does not seem to
be
> possible. I can't believe that you have to enter every single domain user
> as a login in SQL Server. Someone please tell me this is not the case!
>|||"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:e0rMaRFVEHA.2484@.TK2MSFTNGP11.phx.gbl...

> Not required, Create OS Group and all the domain users to that group.
After
> that give the previlages to
> that domain group to SQL Server.
O.K. Thanks, but the whole point of this exercise is to be able to identify
the user at the database level. Will the system_user or current_user
variables be set to the individual or the group?|||> O.K. Thanks, but the whole point of this exercise is to be able to
identify
> the user at the database level. Will the system_user or current_user
> variables be set to the individual or the group?
I answered that myself. It works!|||Hi,
Yes, you are correct.
Thanks
Hari
MCDBA
"Gorge Bush" <bill.gates@.microcock.cum> wrote in message
news:OpwBIeFVEHA.3788@.TK2MSFTNGP12.phx.gbl...
> identify
> I answered that myself. It works!
>|||Refer to information about the predefined Domain Users group at
http://www.microsoft.com/windows200...sp?url=/windows
2000/en/server/help/sag_SEconceptsImpGroups.htm
Then, just grant access to this group.
-Mark Shlimovich