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
Showing posts with label returns. Show all posts
Showing posts with label returns. Show all posts
Monday, March 26, 2012
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
> )
>
>
>
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
> )
>
>
>
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 exec to windows account
When i do
grant exec on sproc1 to 'domain\user'
it returns
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'domain\user'Hi John
Use [ ] instead of quotes:
grant exec on sproc1 to [domain\user]
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"John Doe" <Johndoe@.jd.com> wrote in message
news:Oj4ImmmbIHA.1212@.TK2MSFTNGP05.phx.gbl...
> When i do
> grant exec on sproc1 to 'domain\user'
> it returns
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'domain\user'
>
grant exec on sproc1 to 'domain\user'
it returns
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'domain\user'Hi John
Use [ ] instead of quotes:
grant exec on sproc1 to [domain\user]
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"John Doe" <Johndoe@.jd.com> wrote in message
news:Oj4ImmmbIHA.1212@.TK2MSFTNGP05.phx.gbl...
> When i do
> grant exec on sproc1 to 'domain\user'
> it returns
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'domain\user'
>
Friday, March 23, 2012
Grant Access Error
SQL Server returns an error when I try go grant privileges to a username
containing '.' (dot). The statement goes like this:
GRANT <privileges> on <table_name> TO gh.om
The error message point to the '.' Is there a workaround?
We use the format "sitename"."username" on quite a lot of
serverconfigurations in our company and it will be quite a job to change al
l
the logon ids.
Thanks for any assistance
/Leif S
--
Systems AnalystUse brackets as delimiters:
GRANT <privileges> on <table_name> TO [gh.om]
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Leif S" <LeifS@.discussions.microsoft.com> wrote in message
news:39A5D87F-9801-4013-87CF-FD6A729AEF47@.microsoft.com...
SQL Server returns an error when I try go grant privileges to a username
containing '.' (dot). The statement goes like this:
GRANT <privileges> on <table_name> TO gh.om
The error message point to the '.' Is there a workaround?
We use the format "sitename"."username" on quite a lot of
serverconfigurations in our company and it will be quite a job to change
all
the logon ids.
Thanks for any assistance
/Leif S
--
Systems Analyst|||Thanks, Tom! Problem solved.
/Leif S.
--
Systems Analyst
"Tom Moreau" wrote:
> Use brackets as delimiters:
> GRANT <privileges> on <table_name> TO [gh.om]
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Leif S" <LeifS@.discussions.microsoft.com> wrote in message
> news:39A5D87F-9801-4013-87CF-FD6A729AEF47@.microsoft.com...
> SQL Server returns an error when I try go grant privileges to a username
> containing '.' (dot). The statement goes like this:
> GRANT <privileges> on <table_name> TO gh.om
> The error message point to the '.' Is there a workaround?
> We use the format "sitename"."username" on quite a lot of
> serverconfigurations in our company and it will be quite a job to change
> all
> the logon ids.
> Thanks for any assistance
> /Leif S
> --
> Systems Analyst
>
containing '.' (dot). The statement goes like this:
GRANT <privileges> on <table_name> TO gh.om
The error message point to the '.' Is there a workaround?
We use the format "sitename"."username" on quite a lot of
serverconfigurations in our company and it will be quite a job to change al
l
the logon ids.
Thanks for any assistance
/Leif S
--
Systems AnalystUse brackets as delimiters:
GRANT <privileges> on <table_name> TO [gh.om]
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Leif S" <LeifS@.discussions.microsoft.com> wrote in message
news:39A5D87F-9801-4013-87CF-FD6A729AEF47@.microsoft.com...
SQL Server returns an error when I try go grant privileges to a username
containing '.' (dot). The statement goes like this:
GRANT <privileges> on <table_name> TO gh.om
The error message point to the '.' Is there a workaround?
We use the format "sitename"."username" on quite a lot of
serverconfigurations in our company and it will be quite a job to change
all
the logon ids.
Thanks for any assistance
/Leif S
--
Systems Analyst|||Thanks, Tom! Problem solved.
/Leif S.
--
Systems Analyst
"Tom Moreau" wrote:
> Use brackets as delimiters:
> GRANT <privileges> on <table_name> TO [gh.om]
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Leif S" <LeifS@.discussions.microsoft.com> wrote in message
> news:39A5D87F-9801-4013-87CF-FD6A729AEF47@.microsoft.com...
> SQL Server returns an error when I try go grant privileges to a username
> containing '.' (dot). The statement goes like this:
> GRANT <privileges> on <table_name> TO gh.om
> The error message point to the '.' Is there a workaround?
> We use the format "sitename"."username" on quite a lot of
> serverconfigurations in our company and it will be quite a job to change
> all
> the logon ids.
> Thanks for any assistance
> /Leif S
> --
> Systems Analyst
>
Wednesday, March 21, 2012
Grabbing DATENAME from date only returns January
why is the below only giving me January?
CAST(DATENAME(Month, datepart(month, ph.systemmonth)) AS varchar(15))
What did you expect ?-Jens Suessmeyer.
http://www.sqlserver2005.de|||
I solved it:
Results:
0000002 MA 0000002 25 5.00 January 1 2005
0000002 MA 0000002 25 20.00 January 1 2005
0000002 MA 0000002 25 43.00 January 1 2005
0000002 MA 0000002 25 1264.20 January 6 2005
0000002 MA 0000002 25 1344.47 January 6 2005
0000002 MA 0000002 25 210.23 January 10 2005
0000002 MA 0000002 25 211.25 January 10 2005
Expected Results:
0000002 MA 0000002 25 5.00 January 1 2005
0000002 MA 0000002 25 20.00 January 1 2005
0000002 MA 0000002 25 43.00 January 1 2005
0000002 MA 0000002 25 1264.20 June 6 2005
0000002 MA 0000002 25 1344.47 June 6 2005
0000002 MA 0000002 25 210.23 October 10 2005
0000002 MA 0000002 25 211.25 October 10 2005
Resolution:
CAST(DATENAME(Month, '2006/' + Convert(varchar(2), ph.systemmonth)) + '/1') AS varchar(15)) as SystemMonth
for a real date field (which systemmonth was not in this case...since it only contained the month) you can do this:
CAST(DATENAME(Month, '2006/' + Convert(varchar(2), datepart(mm,getdate())) + '/1') AS varchar(15)) as SystemMonth,
Monday, March 19, 2012
Got Microsoft OLE DB Provider for ODBC Drivers error '80004005' when running Stored Procedures
when i am running a Stored Procedures, system always returns me error message below and Stored Procedures stops. please help
Microsoft OLE DB Provider for ODBC Drivers error
Microsoft OLE DB Provider for ODBC Drivers error
'80004005'
[Microsoft][ODBC SQL Server Driver] Received an unrecognized datatype 0 from
TDS data stream
sometime it returns error messge like, TDS Buffer Length Too Large
or
Unknown token received from SQL Server
or
Protocol error in TDS stream
or
Bad token from SQL Server: Datastream processing out of sync.
or
Invalid cursor state
or
TDS Buffer Link Too Large
or
Function sequence error
Many thanks, Please help, appreciated
Can you post more information about the configuration:
- What version of SQL Server do you use?
- What OLEDB provider do you use (e.g., SQLOLEDB, SQL Native Client?)?
- What is the definition of relevan tables?
- Code for the stored procedure and for the application calling it (omit any confidential information)?
|||
Thanks Peter:
it's SQL Server 2000 sp4, this problem happens on the OLEDB provider ODBC driver. the detials of sproc is that there is one sproc calling other 4 different sprocs to update 4 different table (about 30,000 columns need to be updated in each table). and it will run only once a month automaticly by Job.
I also tried to run those 4 sprocs separately and manually, still get the same problem, somehow.
cheers
|||anyone can help?|||Could be a network performance or configuration error.
http://support.microsoft.com/default.aspx/kb/176256
Got Microsoft OLE DB Provider for ODBC Drivers error '80004005' when running Stored Procedures
when i am running a Stored Procedures, system always returns me error message below and Stored Procedures stops. please help
Microsoft OLE DB Provider for ODBC Drivers error
Microsoft OLE DB Provider for ODBC Drivers error
'80004005'
[Microsoft][ODBC SQL Server Driver] Received an unrecognized datatype 0 from
TDS data stream
sometime it returns error messge like, TDS Buffer Length Too Large
or
Unknown token received from SQL Server
or
Protocol error in TDS stream
or
Bad token from SQL Server: Datastream processing out of sync.
or
Invalid cursor state
or
TDS Buffer Link Too Large
or
Function sequence error
Many thanks, Please help, appreciated
Can you post more information about the configuration:
- What version of SQL Server do you use?
- What OLEDB provider do you use (e.g., SQLOLEDB, SQL Native Client?)?
- What is the definition of relevan tables?
- Code for the stored procedure and for the application calling it (omit any confidential information)?
|||
Thanks Peter:
it's SQL Server 2000 sp4, this problem happens on the OLEDB provider ODBC driver. the detials of sproc is that there is one sproc calling other 4 different sprocs to update 4 different table (about 30,000 columns need to be updated in each table). and it will run only once a month automaticly by Job.
I also tried to run those 4 sprocs separately and manually, still get the same problem, somehow.
cheers
|||anyone can help?|||Could be a network performance or configuration error.
http://support.microsoft.com/default.aspx/kb/176256
Subscribe to:
Posts (Atom)