grant permission on function

hi all,
i know you can grant permission for function by the following code:
grant all on GetEmployeeName to user1
but how can I grant just SELECT permission on Function GetEmployeeName()?You cannot give SELECT permission on user-defined functions. Only thing
you can give is EXEC rights. Other workaround is create a view and give
SELECT permissions on it and call the view in the user defined function.|||GRANT SELECT ON ...
BG, SQL Server MVP
www.SolidQualityLearning.com
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:#ZlNypiNFHA.3928@.TK2MSFTNGP09.phx.gbl...
> hi all,
> i know you can grant permission for function by the following code:
> grant all on GetEmployeeName to user1
>
> but how can I grant just SELECT permission on Function GetEmployeeName()?
>|||Oops; I should be testing before suggesting. 8-)
BG, SQL Server MVP
www.SolidQualityLearning.com
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:#0Qx2ziNFHA.2748@.TK2MSFTNGP10.phx.gbl...
> GRANT SELECT ON ...
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:#ZlNypiNFHA.3928@.TK2MSFTNGP09.phx.gbl...
GetEmployeeName()?
>|||You can grant execute permission on a function just like on a stored
procedure (a function is a type of sproc). Not sure if you can grant select
on a function. I tried with an error...
Richard
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:%230Qx2ziNFHA.2748@.TK2MSFTNGP10.phx.gbl...
> GRANT SELECT ON ...
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:#ZlNypiNFHA.3928@.TK2MSFTNGP09.phx.gbl...
>|||Hi,
Give EXEC permission to the function.
GRANT EXECUTE ON FN_NAME to USER_NAME
Thanks
Hari
SQL Server MVP
____________________________________
Britney Wrote:
hi all,
i know you can grant permission for function by the following code:
grant all on GetEmployeeName to user1
but how can I grant just SELECT permission on Function GetEmployeeName()?
Sent via SreeSharp NewsReader http://www.SreeSharp.com|||Actually, you can if it's a table-valued one. You have to grant EXECUTE
permissions if it's a scalar one.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Richard Ding" <rding@.acadian-asset.com> wrote in message
news:etrRY9jNFHA.3144@.tk2msftngp13.phx.gbl...
> You can grant execute permission on a function just like on a stored
> procedure (a function is a type of sproc). Not sure if you can grant
select
> on a function. I tried with an error...
>
> Richard
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
message
> news:%230Qx2ziNFHA.2748@.TK2MSFTNGP10.phx.gbl...
GetEmployeeName()?
>|||Actually, this scenario depends on the return type of the UDF. If the UDF
is a scalar valued function, the correct permission is EXECUTE. If the
function is table valued, the correct permission is SELECT. Refer to
http://msdn.microsoft.com/library/d...>
ity_5myb.asp
for more information regarding permissions on UDFs.
Assuming that the function is owned by dbo:
Table Valued
GRANT SELECT ON dbo.GetEmployeeName TO user
Scalar
GRANT EXECUTE ON dbo.GetEmployeeName TO user
HTH
--
Dave Fancher
http://davefancher.blogspot.com
"Hari Pra" <hari_pra_k@.hotmail.com> wrote in message
news:uozNfWkNFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Give EXEC permission to the function.
> GRANT EXECUTE ON FN_NAME to USER_NAME
> Thanks
> Hari
> SQL Server MVP
> ____________________________________
> Britney Wrote:
> hi all,
> i know you can grant permission for function by the following code:
> grant all on GetEmployeeName to user1
>
> but how can I grant just SELECT permission on Function GetEmployeeName()?
>
>
>
>
> Sent via SreeSharp NewsReader http://www.SreeSharp.com|||That clarifies it. Thanks.
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:eZrQxwkNFHA.2880@.TK2MSFTNGP10.phx.gbl...
> Actually, you can if it's a table-valued one. You have to grant EXECUTE
> permissions if it's a scalar one.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Richard Ding" <rding@.acadian-asset.com> wrote in message
> news:etrRY9jNFHA.3144@.tk2msftngp13.phx.gbl...
> select
> message
> GetEmployeeName()?
>

Grant All function giving errors

I'm migrating from 2000 to 2005, what is the best way to handle the following error:

The ALL permission is deprecated and maintained only for compatibility. It DOES NOT imply ALL permissions defined on the entity

The code is below:

DECLARE @.sp_name AS sysname;
DECLARE syscursor CURSOR FOR
SELECT name FROM sysobjects
WHERE (xtype = 'P' or xtype='V') AND ((status & 0x80000000) = 0);

OPEN syscursor;
FETCH NEXT FROM syscursor INTO @.sp_name;

WHILE (@.@.FETCH_STATUS = 0)
BEGIN
EXECUTE ('GRANT all ON ' + @.sp_name + ' TO Public');
FETCH NEXT FROM syscursor INTO @.sp_name;
END

CLOSE syscursor;
DEALLOCATE syscursor;

It is not an error, it is a deprecation warning. The grant still works, for now, but ALL will be removed in a future version of SQL Server.

In SQL Server 2005, we have introduced new permissions. ALL does not include these new permissions (otherwise, existing programs might end up running with higher permissions in SQL Server 2005 than they did in SQL Server 2000). So the warning is for letting you know that ALL doesn't really mean "all permissions" anymore.

You should replace GRANT ALL with specific grants of the permissions you want to GRANT, for example, in your case, you can just replace GRANT ALL with GRANT EXECUTE, as EXECUTE is the only permission implied by ALL on stored procedures.

For additional information on ALL, see: http://msdn2.microsoft.com/en-us/library/ms188371.aspx. Actually, now that I look at it, the stored procedure permissions list is incorrect, it should only include EXECUTE, as I mentioned above. I'll file a bug to have the documentation corrected.

Thanks
Laurentiu

|||Commands like "grant all to admin" used to grant all permissions for all objects available in the database. What is the equivalent of this in SQL Server 2005 (it does not need to grant the new CONTROL right in my case)?

|||

CONTROL on the database is probably the closest. You can also make admin a member of db_owner. But there is no real replacement for ALL.

Thanks
Laurentiu

sql

Grant All function giving errors

I'm migrating from 2000 to 2005, what is the best way to handle the following error:

The ALL permission is deprecated and maintained only for compatibility. It DOES NOT imply ALL permissions defined on the entity

The code is below:

DECLARE @.sp_name AS sysname;
DECLARE syscursor CURSOR FOR
SELECT name FROM sysobjects
WHERE (xtype = 'P' or xtype='V') AND ((status & 0x80000000) = 0);

OPEN syscursor;
FETCH NEXT FROM syscursor INTO @.sp_name;

WHILE (@.@.FETCH_STATUS = 0)
BEGIN
EXECUTE ('GRANT all ON ' + @.sp_name + ' TO Public');
FETCH NEXT FROM syscursor INTO @.sp_name;
END

CLOSE syscursor;
DEALLOCATE syscursor;

It is not an error, it is a deprecation warning. The grant still works, for now, but ALL will be removed in a future version of SQL Server.

In SQL Server 2005, we have introduced new permissions. ALL does not include these new permissions (otherwise, existing programs might end up running with higher permissions in SQL Server 2005 than they did in SQL Server 2000). So the warning is for letting you know that ALL doesn't really mean "all permissions" anymore.

You should replace GRANT ALL with specific grants of the permissions you want to GRANT, for example, in your case, you can just replace GRANT ALL with GRANT EXECUTE, as EXECUTE is the only permission implied by ALL on stored procedures.

For additional information on ALL, see: http://msdn2.microsoft.com/en-us/library/ms188371.aspx. Actually, now that I look at it, the stored procedure permissions list is incorrect, it should only include EXECUTE, as I mentioned above. I'll file a bug to have the documentation corrected.

Thanks
Laurentiu

|||Commands like "grant all to admin" used to grant all permissions for all objects available in the database. What is the equivalent of this in SQL Server 2005 (it does not need to grant the new CONTROL right in my case)?

|||

CONTROL on the database is probably the closest. You can also make admin a member of db_owner. But there is no real replacement for ALL.

Thanks
Laurentiu

got seconds but need minutes

I have a result that comes out in number of seconds, but need to see it converted to minutes and hours and seconds. Is there a convert function that would do this?
Thanks,
DanDECLARE @.x int

SELECT @.x = 3600 + 1800

SELECT @.x/60/60 AS Hours,CONVERT(Int,(@.x/60.00/60.00-@.x/60/60)*60) AS Seconds|||I'm rather fond of:SELECT Convert(CHAR(8), DateAdd(second, 45296, '0:00'), 8)-PatP|||I like it!

Simple, Elegant...sql

GoalSeek in SQL Server?

Has anyone create a GoalS function similar to that of Excel? I've
been researching this and trying to script one out myself without much
sucess. If someone could tell me that its impossible, that would be
helpful too. TIA.This is not really what SQL is used for.|||You may be able to do it in MDX using multiple-pass calculations to iterate
to the required result:
http://msdn.microsoft.com/library/d...
anced_6jn7.asp
In pure SQL you could perhaps do it without iteration if the range of
possible values is small enough:
SELECT TOP 1 N1.num, N2.num
FROM some_numbers AS N1, some_numbers AS N2
WHERE (N1.num + (N2.num/2) - 123) = 12345
ORDER BY N1.num, N2.num
David Portas
SQL Server MVP
--|||I have used SQL to solve number puzzles, just for fun (eh!?). Try the
following script.
-- Easy example, all combinations of the numbers 1-9 which add up to 11
DECLARE @.control TABLE ( control_no TINYINT PRIMARY KEY, control_str AS CAST
(control_no AS CHAR(1) ) )
DECLARE @.i INT
SET NOCOUNT ON
SET @.i = 0
-- Add control numbers to temp table
WHILE @.i Between 0 And 9
BEGIN
INSERT @.control VALUES( @.i )
SET @.i = @.i + 1
END
SET NOCOUNT OFF
-- List all combinations which add up to 11
SELECT a.control_no, b.control_no, a.control_no + b.control_no
FROM @.control a, @.control b
WHERE a.control_no + b.control_no = 11
-- Harder example; MENSA-type substitution puzzle
-- Mensa's Number Puzzles for Math Geniuses by Harold Gale has this puzzle:
--
-- Number Puzzle 58
-- Place six three digit numbers of 100 plus at the end of 685
-- so that six numbers of six digits are produced [like 685123].
-- When each number is divided by 111 six whole numbers can be found.
SELECT
a.control_str,
b.control_str,
c.control_str,
a.control_str + b.control_str + c.control_str AS result,
'685' + a.control_str + b.control_str + c.control_str AS result2,
CAST( '685' + a.control_str + b.control_str + c.control_str AS DECIMAL ) /
111 AS result2
FROM @.control a, @.control b, @.control c
WHERE a.control_str + b.control_str + c.control_str > 100
AND ( CAST( '685' + a.control_str + b.control_str + c.control_str AS
DECIMAL ) / 111 ) =
CAST ( CAST( '685' + a.control_str + b.control_str + c.control_str AS
DECIMAL ) / 111 AS INT )
ORDER BY 1
-- This puzzle is similar to ones like this:
-- How many three digit numbers are divisible by 17?
-- Well, the first one is greater than 100. 100/17=5.88235. . . So the first
is 6x17=102. We don't need to list these three digit numbers. The last one i
s
less than 1000. 1000/17=58.8235. . . So our three digit numbers are 6x17,
7x17, 8x17, . . ., 58x17. There are 53 such numbers.
SELECT
a.control_str,
b.control_str,
c.control_str,
a.control_str + b.control_str + c.control_str AS result,
CAST( a.control_str + b.control_str + c.control_str AS DECIMAL ) / 17 AS
result2
FROM @.control a, @.control b, @.control c
WHERE a.control_str + b.control_str + c.control_str > 100
AND ( CAST( a.control_str + b.control_str + c.control_str AS DECIMAL ) /
17 ) =
CAST ( CAST( a.control_str + b.control_str + c.control_str AS DECIMAL ) /
17 AS INT )
ORDER BY 1
-- etc
Obviously the code above is quick and dirty, but you see where I'm going.
If not, look up 'Cartesian products'. Don't go using it in production
environment!
Let me know hot you get on.
Damien
"carmaboy@.gmail.com" wrote:

> Has anyone create a GoalS function similar to that of Excel? I've
> been researching this and trying to script one out myself without much
> sucess. If someone could tell me that its impossible, that would be
> helpful too. TIA.
>|||Did you notice that you wind up generating all possible combinations|||Yes, I'm using the cartesian product to get the answers to the puzzles. Is
that what you mean? Presumably there are other ways to do this, but this wa
s
just a demo, and hey it works!
Damien
"--CELKO--" wrote:

> Did you notice that you wind up generating all possible combinations
>|||Perhaps Joe's point was that declarative SQL solutions generally will
produce the total set of results, effort that is highly redundant if
all solutions are equal. What you probably wanted was just the fastest
single solution. That's why SQL is likely a heavily inefficient
solution - ok for solving recreational problems but in a commercial
environment you'll probably be better off with a procedural language or
a math/stats package.
David Portas
SQL Server MVP
--|||>> Perhaps Joe's point was that declarative SQL solutions generally will pro
duce the total set of results, <<
In fact, I just a did a short piece for DBAzine with a simple bin
packing problem (one bin, n-items) to demonstrate how the answer space
keeps doubling.

Globale Function

Hi !

Is it possible to declare a gloable function in reportServer that all reports could access? For each reports that use the same function i've declared it in the code property of the rapport but it will be easier if i could share the function for all reports.

Thanks !

You could move the functions into a custom assembly. Check the "custom assembly" section of this BOL page: http://msdn2.microsoft.com/en-us/library/ms155798.aspx

-- Robert

Global Function in 2005

Hi,
I'm trying to create a global function in SQL Server 2005. I mean, a
function similar to GETDATE(), that is public for all databases.
This is possible in SQL Server 2000, but I don't find any similar in
2005
Thanks in advance
--
--
--
Un saludo
--
---
"Slo s que no s nada. " (Scrates)Carlos Sacristn (csacristanARROBAmvpsPUNTOorg) writes:
> I'm trying to create a global function in SQL Server 2005. I mean, a
> function similar to GETDATE(), that is public for all databases.
> This is possible in SQL Server 2000, but I don't find any similar in
> 2005
I guess this was possible in SQL 2000 by creating functions in master and
make them look like system functions as fn_get_trace_status(). However,
that was entirely unsupported.
In SQL 2005, system procedures and system functions are stored in the
invisible resource database.
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|||Thanks Edgar,
I know this was unsupported, but the applications that actually run
against SQL Server 2000 use this feature, so I trying to do the same in
2005. Do you know any way to get something similar?
I try with synonyms, but it's no possible
--
--
--
Un saludo
--
---
"Slo s que no s nada. " (Scrates)
"Erland Sommarskog" <esquel@.sommarskog.se> escribi en el mensaje
news:Xns97D38A59F23A4Yazorman@.127.0.0.1...
> Carlos Sacristn (csacristanARROBAmvpsPUNTOorg) writes:
> I guess this was possible in SQL 2000 by creating functions in master and
> make them look like system functions as fn_get_trace_status(). However,
> that was entirely unsupported.
> In SQL 2005, system procedures and system functions are stored in the
> invisible resource database.
>
> --
> 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|||Carlos Sacristn (csacristanARROBAmvpsPUNTOorg) writes:
> I know this was unsupported, but the applications that actually run
> against SQL Server 2000 use this feature, so I trying to do the same in
> 2005. Do you know any way to get something similar?
Moral: don't rely on unsupported and undocumented behaviour.

> I try with synonyms, but it's no possible
Synonyms sounds as the best bet, but you would have to install them in
every database. And the functions can not operate on tables in the local
database.
You will have to back to the drawing-board, I guess.
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|||Bad news, really...
In my case, I don't need the functions to operate with tables. There are
scalar function, for example, something like
CREATE FUNCTION dbo.fn_Trim (@.par VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
RETURN RTRIM(LTRIM(@.par))
END
I think that the possibility to create this type of scalar-functions
(globals for all databases) could be an interesting feature.
Anyway, thanks for your help Erland
--
--
--
Un saludo
--
---
"Slo s que no s nada. " (Scrates)
"Erland Sommarskog" <esquel@.sommarskog.se> escribi en el mensaje
news:Xns97D39FFD079A1Yazorman@.127.0.0.1...
> Carlos Sacristn (csacristanARROBAmvpsPUNTOorg) writes:
> Moral: don't rely on unsupported and undocumented behaviour.
>
> Synonyms sounds as the best bet, but you would have to install them in
> every database. And the functions can not operate on tables in the local
> database.
> You will have to back to the drawing-board, I guess.
> --
> 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|||Carlos Sacristn (csacristanARROBAmvpsPUNTOorg) writes:
> In my case, I don't need the functions to operate with tables. There
> are scalar function, for example, something like
> CREATE FUNCTION dbo.fn_Trim (@.par VARCHAR(8000))
> RETURNS VARCHAR(8000)
> AS
> BEGIN
> RETURN RTRIM(LTRIM(@.par))
> END
> I think that the possibility to create this type of scalar-functions
> (globals for all databases) could be an interesting feature.
On http://lab.msdn.microsoft.com/ProductFeedback/ you can submit a
suggestion for this to be added ot a future release of SQL Server.
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

Global function for different different task

Hi,

I've a function which I'm using in one of my script task. Then I do some operations and in other script task I'm using same function. If I change a function in one task then I need to do in all other tasks. Is there a way where we can write this function at package level and be able to use in any of the task.

Regards

Munafbhai

You cannot do this with scripts in packages. You can however create a managed dll library (in C-sharp or Vb) and make a reference to this dll in your script package.

Sometimes when you use SQL server or another relational DB, you could use stored procedure, to avoid cutting and pasting code in several package scripts.

global code

Hi ,
I wrote a function in the report property code :
Public Function AA(field as string)
AA = field
End Function
When I try to write an expretion like :
=AA(Me.Value)
I get and error of AA is not recognize.
What is the problem ?
10x.You need to call your function as =Code.AA(Me.Value) . This will
solve your problem mentioned error.
I am doubtfull about what you exactly mean by Me.Vlaue
Hope this helps.
Thanks,
Mahesh