Grant Execute to user on procedures

Hi,
is there a easy way of grantinng Execute-privilegies to user ABC for all
procedures named 'MTS*' in a database?
regards,
Bent S. Lund
System Developer
MCP VB
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!Hi,
In Query Analyzer execute the below script with Text result.
Use <dbname>
go
select 'grant execute on '+name +' to ABC' from sysobjects where name like
'MTS%' and type='P'
-- The above script will generate a script to grant execute previlage to ABC
user for all procedures start with MTS%.
Copy the result window and paste in a new Query analyzer window and execute
it.
Thanks
Hari
MCDBA
"Bent Lund" <bstlu@.online.no> wrote in message
news:ucjryKQYEHA.3012@.tk2msftngp13.phx.gbl...
> Hi,
> is there a easy way of grantinng Execute-privilegies to user ABC for all
> procedures named 'MTS*' in a database?
>
> regards,
> Bent S. Lund
> System Developer
> MCP VB
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Have a look at
Granting execute permissions to all stored procedures in a database
http://www.sqldbatips.com/showarticle.asp?ID=8
and sp_grantexec
http://www.sqldbatips.com/showcode.asp?ID=2
You can use this like
exec sp_grantexec 'ABC','MTS%'
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Bent Lund" <bstlu@.online.no> wrote in message
news:ucjryKQYEHA.3012@.tk2msftngp13.phx.gbl...
> Hi,
> is there a easy way of grantinng Execute-privilegies to user ABC for all
> procedures named 'MTS*' in a database?
>
> regards,
> Bent S. Lund
> System Developer
> MCP VB
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!

Grabbing a calculated field for a new calculation

I'm going to try and explain this as best as I can :)
I have calculated fields that has the following expressions - I've
named each textbox hoping that I could grab them in the expression but
I have no idea how':
1. totalMarket: =iif(sum(Fields!Wins.Value) = 0, 0, sum( Fields!
Wins.Value) + iif(sum(Fields!Pending.Value) = 0, 0, sum(Fields!
Pending.Value))) - iif(sum(Fields!Losses.Value) = 0, 0, sum(Fields!
Losses.Value))
2. remainingSales = =iif(Sum(Fields!Pending.Value)=0, 0, Sum(Fields!
Pending.Value))
I need the next text box (marketPercent) to do the following...
totalMarket - remainingSales / totalMarket
I used all the expression above to make it work - but there has to be
an easier way.. I tried using..
=iif(Sum(ReportItem!totalMarket.Value)) ... etc but that syntax did
not work... does anyone know how to grab the name of the textbox in
an expression?
ps - this is not a header or a footer - it's in the actual form
Thanks in advance!
LisaOn Jun 6, 10:38 am, peashoe <peas...@.yahoo.com> wrote:
> I'm going to try and explain this as best as I can :)
> I have calculated fields that has the following expressions - I've
> named each textbox hoping that I could grab them in the expression but
> I have no idea how':
> 1. totalMarket: =iif(sum(Fields!Wins.Value) = 0, 0, sum( Fields!
> Wins.Value) + iif(sum(Fields!Pending.Value) = 0, 0, sum(Fields!
> Pending.Value))) - iif(sum(Fields!Losses.Value) = 0, 0, sum(Fields!
> Losses.Value))
> 2. remainingSales = =iif(Sum(Fields!Pending.Value)=0, 0, Sum(Fields!
> Pending.Value))
> I need the next text box (marketPercent) to do the following...
> totalMarket - remainingSales / totalMarket
> I used all the expression above to make it work - but there has to be
> an easier way.. I tried using..
> =iif(Sum(ReportItem!totalMarket.Value)) ... etc but that syntax did
> not work... does anyone know how to grab the name of the textbox in
> an expression?
> ps - this is not a header or a footer - it's in the actual form
> Thanks in advance!
> Lisa
As far as I know, referencing the textbox control by an alias is not
possible. Referencing the textbox by the expression used is most
likely the only way to reference it. Sorry that I could not be of
greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultantsql

Got err: There is already an object named '#TEMP' in the database.

I have the drop table #temp at the end of my stored prodedure but I think it
got executed half way and left the #temp there. I try executing the drop
table #temp but it says there is not #temp table. But if I run the alter
procedure again then I get the err message that the #temp table already
exist.
I tried stop and start the sql engine but that doesn't work. I restarted
the PC and it still won't go away. Does anyone know how to get rid of this?
Thanks, AlphaBefore you try to create the #temp table,
IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL
DROP TABLE #TEMP
Of course, your initial problem should have gone away if you opened a new
session in Query Analyzer.
A
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:13DC4502-7F82-44B9-B123-E35043837DDE@.microsoft.com...
>I have the drop table #temp at the end of my stored prodedure but I think
>it
> got executed half way and left the #temp there. I try executing the drop
> table #temp but it says there is not #temp table. But if I run the alter
> procedure again then I get the err message that the #temp table already
> exist.
> I tried stop and start the sql engine but that doesn't work. I restarted
> the PC and it still won't go away. Does anyone know how to get rid of
> this?
> Thanks, Alpha|||This just gets worse. I change the #temp name to many different other names
and I keep getting the same message that it already exist.
"Alpha" wrote:

> I have the drop table #temp at the end of my stored prodedure but I think
it
> got executed half way and left the #temp there. I try executing the drop
> table #temp but it says there is not #temp table. But if I run the alter
> procedure again then I get the err message that the #temp table already
> exist.
> I tried stop and start the sql engine but that doesn't work. I restarted
> the PC and it still won't go away. Does anyone know how to get rid of thi
s?
> Thanks, Alpha|||Maybe you could show your code...
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:0F8B1D0C-6CD7-42D5-BE0E-4D473E39C188@.microsoft.com...
> This just gets worse. I change the #temp name to many different other
> names
> and I keep getting the same message that it already exist.

Got err: There is already an object named '#TEMP' in the datab

Thank you for the reply. I did figured it out last Friday after I keep
messing with it. I have a if -else code where the if xxx then insert yyy to
#temp else insert zzz to #temp. I didn't know SQL doesn't allow #temp to be
used in this fashion which is usually allowed in development coding. I
changed the else to insert to #temp2 and it worked.
Thanks, Alpha
"Aaron Bertrand [SQL Server MVP]" wrote:

> Maybe you could show your code...
>
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:0F8B1D0C-6CD7-42D5-BE0E-4D473E39C188@.microsoft.com...
>
>> #temp else insert zzz to #temp. I didn't know SQL doesn't allow #temp to
> be
> used in this fashion which is usually allowed in development coding.
Well, it doesn't actually EXECUTE the code, so it doesn't understand that
your IF/ELSE can only result in one path. It merely sees you trying to
create the same #temp table twice...|||I see. Thank you for your help.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Well, it doesn't actually EXECUTE the code, so it doesn't understand that
> your IF/ELSE can only result in one path. It merely sees you trying to
> create the same #temp table twice...
>
>