Grant Win Acct Permission

I'd like to grant a WIndows account permission to connect to a db and
exec stored procedures. But am having trouble.

I want this type of effect but can't get the syntax correct:

USE MyDB
GO
CREATE USER 127.0.0.1\ASPNET --ASPNET Account for current machine
GO

GRANT EXECUTE ON AllStoredPRocs TO 127.0.0.1\ASPNET

How is this done in a t-sql script?

Thanks for any help.(wackyphill@.yahoo.com) writes:
> I'd like to grant a WIndows account permission to connect to a db and
> exec stored procedures. But am having trouble.
> I want this type of effect but can't get the syntax correct:
> USE MyDB
> GO
> CREATE USER 127.0.0.1\ASPNET --ASPNET Account for current machine
> GO
> GRANT EXECUTE ON AllStoredPRocs TO 127.0.0.1\ASPNET
> How is this done in a t-sql script?

Since 127.0.0.1\ASPNET is one identifier as far SQL Server is concerned,
you need to put it brackets: [127.0.0.1\ASPNET]. Then whether that
actually works is another matter. You should probably use machine name
rather than 127.0.0.1.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

grant permission problem on functions that returns table data type

problem on grant permission to user
Was this post helpful ?
I have two kinds of functions in the database that need to grant exec
permission to user appUser.
The first kind of function return ordinary datatype, let's call it
funcReturnDataType here.
The second kind of function return table datatype, let's call it
funcReturnTable
When I issued the folloing command to appUser, no problem.
grant exec on funcReturnDataType to appUser
However when I issued:
grant exec on funcReturnTable to appUser
I got the following error message:
Server: Msg 4606, Level 16, State 1, Line 1
Granted or revoked privilege EXECUTE is not compatible with object.
Any suggestions to resolve this problem?
Thank you!
--
The following is an example of a function that returns a table data type:
CREATE FUNCTION funcReturnTable
(
@.i int
)
RETURNS TABLE
AS
RETURN
(
Select Distinct FoodType from Food where region=@.i
)For a table-valued function, specify SELECT instead of EXEC.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"she" <she@.discussions.microsoft.com> wrote in message
news:3C2893C3-7394-40CE-A098-57C06ED96C57@.microsoft.com...
> problem on grant permission to user
> Was this post helpful ?
>
> I have two kinds of functions in the database that need to grant exec
> permission to user appUser.
> The first kind of function return ordinary datatype, let's call it
> funcReturnDataType here.
> The second kind of function return table datatype, let's call it
> funcReturnTable
> When I issued the folloing command to appUser, no problem.
> grant exec on funcReturnDataType to appUser
> However when I issued:
> grant exec on funcReturnTable to appUser
> I got the following error message:
> Server: Msg 4606, Level 16, State 1, Line 1
> Granted or revoked privilege EXECUTE is not compatible with object.
> Any suggestions to resolve this problem?
> Thank you!
> --
> The following is an example of a function that returns a table data type:
> CREATE FUNCTION funcReturnTable
> (
> @.i int
> )
> RETURNS TABLE
> AS
> RETURN
> (
> Select Distinct FoodType from Food where region=@.i
> )
>
>
>sql

grant permission problem on functions that returns table data type

problem on grant permission to user
Was this post helpful ?
I have two kinds of functions in the database that need to grant exec
permission to user appUser.
The first kind of function return ordinary datatype, let's call it
funcReturnDataType here.
The second kind of function return table datatype, let's call it
funcReturnTable
When I issued the folloing command to appUser, no problem.
grant exec on funcReturnDataType to appUser
However when I issued:
grant exec on funcReturnTable to appUser
I got the following error message:
Server: Msg 4606, Level 16, State 1, Line 1
Granted or revoked privilege EXECUTE is not compatible with object.
Any suggestions to resolve this problem?
Thank you!
The following is an example of a function that returns a table data type:
CREATE FUNCTION funcReturnTable
(
@.i int
)
RETURNS TABLE
AS
RETURN
(
Select Distinct FoodType from Food where region=@.i
)
For a table-valued function, specify SELECT instead of EXEC.
Hope this helps.
Dan Guzman
SQL Server MVP
"she" <she@.discussions.microsoft.com> wrote in message
news:3C2893C3-7394-40CE-A098-57C06ED96C57@.microsoft.com...
> problem on grant permission to user
> Was this post helpful ?
>
> I have two kinds of functions in the database that need to grant exec
> permission to user appUser.
> The first kind of function return ordinary datatype, let's call it
> funcReturnDataType here.
> The second kind of function return table datatype, let's call it
> funcReturnTable
> When I issued the folloing command to appUser, no problem.
> grant exec on funcReturnDataType to appUser
> However when I issued:
> grant exec on funcReturnTable to appUser
> I got the following error message:
> Server: Msg 4606, Level 16, State 1, Line 1
> Granted or revoked privilege EXECUTE is not compatible with object.
> Any suggestions to resolve this problem?
> Thank you!
> --
> The following is an example of a function that returns a table data type:
> CREATE FUNCTION funcReturnTable
> (
> @.i int
> )
> RETURNS TABLE
> AS
> RETURN
> (
> Select Distinct FoodType from Food where region=@.i
> )
>
>
>

grant permission problem on functions that returns table data type

problem on grant permission to user
Was this post helpful ?
I have two kinds of functions in the database that need to grant exec
permission to user appUser.
The first kind of function return ordinary datatype, let's call it
funcReturnDataType here.
The second kind of function return table datatype, let's call it
funcReturnTable
When I issued the folloing command to appUser, no problem.
grant exec on funcReturnDataType to appUser
However when I issued:
grant exec on funcReturnTable to appUser
I got the following error message:
Server: Msg 4606, Level 16, State 1, Line 1
Granted or revoked privilege EXECUTE is not compatible with object.
Any suggestions to resolve this problem?
Thank you!
--
The following is an example of a function that returns a table data type:
CREATE FUNCTION funcReturnTable
(
@.i int
)
RETURNS TABLE
AS
RETURN
(
Select Distinct FoodType from Food where region=@.i
)For a table-valued function, specify SELECT instead of EXEC.
Hope this helps.
Dan Guzman
SQL Server MVP
"she" <she@.discussions.microsoft.com> wrote in message
news:3C2893C3-7394-40CE-A098-57C06ED96C57@.microsoft.com...
> problem on grant permission to user
> Was this post helpful ?
>
> I have two kinds of functions in the database that need to grant exec
> permission to user appUser.
> The first kind of function return ordinary datatype, let's call it
> funcReturnDataType here.
> The second kind of function return table datatype, let's call it
> funcReturnTable
> When I issued the folloing command to appUser, no problem.
> grant exec on funcReturnDataType to appUser
> However when I issued:
> grant exec on funcReturnTable to appUser
> I got the following error message:
> Server: Msg 4606, Level 16, State 1, Line 1
> Granted or revoked privilege EXECUTE is not compatible with object.
> Any suggestions to resolve this problem?
> Thank you!
> --
> The following is an example of a function that returns a table data type:
> CREATE FUNCTION funcReturnTable
> (
> @.i int
> )
> RETURNS TABLE
> AS
> RETURN
> (
> Select Distinct FoodType from Food where region=@.i
> )
>
>
>

Got a unicode error during copying database

I got following problems during copying database around different hosts.

Could anyone help me?

BR.
nathan

Event Type: Error
Event Source: SQLISPackage
Event Category: None
Event ID: 12550
Date: 8/15/2007
Time: 2:53:51 AM
User: NT AUTHORITY\SYSTEM
Computer: DB1
Description:
Event Name: OnError
Message: ERROR : errorCode=-1071636471 description=SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unicode conversion failed".
helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
StackTrace: at Microsoft.SqlServer.Management.Dts.DtsTransferProvider.ExecuteTransfer()
at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()
Operator: NT AUTHORITY\SYSTEM
Source Name: db_pushfun_com_DB1_Transfer Objects Task
Source ID: {939367C5-D98E-4C92-9688-9F32595DB981}
Execution ID: {09E39535-95E0-47EC-B49C-803B0986B1BB}
Start Time: 8/15/2007 2:53:51 AM
End Time: 8/15/2007 2:53:51 AM
Data Code: 0

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Well, I apologize if I am stating the obvious but it appears that you are trying to move a "Unicode" data type into a non-unicode target. Check your source and target databases for the data type of the fields. If they are different then you need to use the appropriate Type Case in a Data Conversion task to convert the source format to your target format.|||Actually, I'm copying a database not table. It's so strange that I could copy databases with unicode data from different hosts.

good practice?

A couple of questions.
1)
I have a column Event_End_Date with smalldatetime data type. I also have
following update scripts that gets executed every day. Basically I do not
care about the time portion of smalldatetime (or rather it needs to be
default time format 00:00:00)
UPDATE tblEvents
SET Event_End_Date = CONVERT(VARCHAR, GETDATE(), 101)
Now is it more proper to program it following way? I know in other
programming language like Java, C++, you would always have to explicitly
cast it to the appropriate data type. How sensitive do I need to be when it
comes to casting in TSQL?
UPDATE tblEvents
SET Event_End_Date = CAST(CONVERT(VARCHAR, GETDATE(), 101) AS SMALLDATETIME)
2) I have read somewhere that the following query needs to be rewritten
SELECT *
FROM tblEvents
WHERE Event_End_Date = '6/7/2006'
to
SELECT *
FROM tblEvents
WHERE DATEPART(YEAR, Event_End_Date) = 2006 AND DATEPART(MONTH,
Event_End_Date) = 6 AND DATEPART(Day, Event_End_Date) = 7
Of course, should there be an index Event_End_Date, it is useless with the
above query. What's your opinion on this matter relating to datetime data
types?
Thanks all.Justin,
If you use style 112, you do not have to worry about casting because sql
server will always interprets the value correctly as a datetime value, not
matter what language or date format is using your server.
UPDATE tblEvents
SET Event_End_Date = CONVERT(varchar(8), GETDATE(), 112)
If you do not pass the time portion from the client application, sql server
will default it to 00:00:00.000

> 2) I have read somewhere that the following query needs to be rewritten
> SELECT *
> FROM tblEvents
> WHERE Event_End_Date = '6/7/2006'
> to
> SELECT *
> FROM tblEvents
> WHERE DATEPART(YEAR, Event_End_Date) = 2006 AND DATEPART(MONTH,
> Event_End_Date) = 6 AND DATEPART(Day, Event_End_Date) = 7
Not really. If the values in the column has time portion equal to
"00:00:00.000", then it will work without problems. If the values in the
column include tiem portion other that 12 AM, then you should use the patter
n:
...
where
Event_End_Date >= convert(varchar(8), @.d, 112)
Event_End_Date < dateadd(day, 1, convert(varchar(8), @.d, 112))
This way, sql server will make a proper use of an index if this exists. When
you manipulate the column in the "where" clause, sql server does not conside
r
the expresion as a search argument.
Example:
..
where
Event_End_Date >= '20060607'
Event_End_Date < '20060608'
The ultimate guide to the datetime datatypes
http://www.karaszi.com/SQLServer/info_datetime.asp
Should I use BETWEEN in my database queries?
http://www.aspfaq.com/show.asp?id=2280
AMB
"Justin" wrote:

> A couple of questions.
> 1)
> I have a column Event_End_Date with smalldatetime data type. I also have
> following update scripts that gets executed every day. Basically I do not
> care about the time portion of smalldatetime (or rather it needs to be
> default time format 00:00:00)
> UPDATE tblEvents
> SET Event_End_Date = CONVERT(VARCHAR, GETDATE(), 101)
> Now is it more proper to program it following way? I know in other
> programming language like Java, C++, you would always have to explicitly
> cast it to the appropriate data type. How sensitive do I need to be when
it
> comes to casting in TSQL?
> UPDATE tblEvents
> SET Event_End_Date = CAST(CONVERT(VARCHAR, GETDATE(), 101) AS SMALLDATETIM
E)
> 2) I have read somewhere that the following query needs to be rewritten
> SELECT *
> FROM tblEvents
> WHERE Event_End_Date = '6/7/2006'
> to
> SELECT *
> FROM tblEvents
> WHERE DATEPART(YEAR, Event_End_Date) = 2006 AND DATEPART(MONTH,
> Event_End_Date) = 6 AND DATEPART(Day, Event_End_Date) = 7
> Of course, should there be an index Event_End_Date, it is useless with the
> above query. What's your opinion on this matter relating to datetime data
> types?
> Thanks all.
>
>