October 27, 2009 at 3:02 pm
I am using SS Mgmt Studio 2005 against a 2000 db. I write stored procs and the sys admin wants me to add code at the bottom of the proc, giving EXEC rights to user/role "public" so that he doesn't have to do this manually. I can find code for SS2005:
GRANT EXEC ON TransHistoryAccount20091016_pr TO public
but I can't find anything similar on SS2000. Anyone have a solution? Thanks!
October 27, 2009 at 3:41 pm
gardenlady (10/27/2009)
I am using SS Mgmt Studio 2005 against a 2000 db. I write stored procs and the sys admin wants me to add code at the bottom of the proc, giving EXEC rights to user/role "public" so that he doesn't have to do this manually. I can find code for SS2005:GRANT EXEC ON TransHistoryAccount20091016_pr TO public
but I can't find anything similar on SS2000. Anyone have a solution? Thanks!
The above syntax works on 2000 also. Have you tried running the GRANT statement on the SS2000 server? Does it give you error?
For more info check this out.
HTH,
Supriya
October 27, 2009 at 3:48 pm
Yes I tried it (db_owner) and the sys admin tried it. It doesn't work for either of us.
I'm doing it in a dev environment and when I look at the stored proc, there are no users/roles in the Permissions screen.
October 27, 2009 at 4:05 pm
I have the same setup; using SSMS to query SS2000 servers. I ran the grant statement for one of my stored procedures and it worked fine. I am sure you must have done this but can you check to make sure you have the right stored proc name, selecting the right database and instance and have hit refresh.
October 27, 2009 at 4:17 pm
Is there only one procedure named "TransHistoryAccount20091016_pr"?
_____________
Code for TallyGenerator
October 28, 2009 at 7:18 am
There's only one procedure with that name. When I right-click on it and go to Permissions, there are no Users or Roles in the top and no Explicit Permissions in the bottom of the window.
Beginning of proc:
USE [prod]
GO
/****** Object: StoredProcedure [dbo].[TransHistoryAccount20091016_pr] Script Date: 10/27/2009 09:13:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if object_id('DBO.TransHistoryAccount20091016_pr') is not null
drop procedure DBO.TransHistoryAccount20091016_pr
GO
CREATE PROCEDURE [dbo].[TransHistoryAccount20091016_pr]
@Company INT
,@Accounts CHAR(100)
,@PStartDate nvarchar(6)
,@PEndDate nvarchar(6)
,@AccountingUnit CHAR(100)
,@BegSubAcct INT
,@EndSubAcct INT
,@LRange Bit
,@BalActOptions INT
,@ShowFLCAPCABal BIT
,@ShowAcctBegEndBal BIT
And at the end:
GRANT EXEC ON TransHistoryAccount20091016_pr TO public
October 28, 2009 at 7:21 am
When I actually run the stored proc (to get data), then I have public in the Users or Roles section of the Permissions window. In the Explicit permissions for publick:
dbo Grantor has Execute permissions (Grant is checked).
October 28, 2009 at 10:33 am
gardenlady (10/28/2009)
When I actually run the stored proc (to get data), then I have public in the Users or Roles section of the Permissions window. In the Explicit permissions for publick:dbo Grantor has Execute permissions (Grant is checked).
This is happening because your GRANT statement is within the definition of the stored proc. The GRANT statement is executed only when you run your SP not before that. If you don't want that, then specify the grant after the proc definition like this:
USE [prod]
GO
/****** Object: StoredProcedure [dbo].[TransHistoryAccount20091016_pr] Script Date: 10/27/2009 09:13:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if object_id('DBO.TransHistoryAccount20091016_pr') is not null
drop procedure DBO.TransHistoryAccount20091016_pr
GO
CREATE PROCEDURE [dbo].[TransHistoryAccount20091016_pr]
@Company INT
,@Accounts CHAR(100)
,@PStartDate nvarchar(6)
,@PEndDate nvarchar(6)
,@AccountingUnit CHAR(100)
,@BegSubAcct INT
,@EndSubAcct INT
,@LRange Bit
,@BalActOptions INT
,@ShowFLCAPCABal BIT
,@ShowAcctBegEndBal BIT
AS
BEGIN
<Your code>
END
GO
GRANT EXEC ON TransHistoryAccount20091016_pr TO public
GO
And save the file to your source control. Does that make sense?
-Supriya
October 28, 2009 at 10:48 am
The stored proc ends up creating a table of data that populates a report (is a data source for a Crystal report). So I can't do
END
GO
GRANT EXEC ON TransHistoryAccount20091016_pr TO public
GO
its' more like:
SELECT COMPANY
,NAME
,ACCOUNT
,SUB_ACCOUNT
,ACCT_UNIT
,FISCAL_YEAR
,TMONTH
,VAR_LEVELS
,DEBITS
,CREDITS
,BEGBAL
,RUNBAL
,ACCTBEGBAL
,ACCTENDBAL
,FLCAPCAENDBAL
,POSTINGDATE
,UPDATEDATE
,STARTDATE
,ENDDATE
,SYSTEMCODE
,JE
,TRANSDESCR
,ACCT_UNITNAME
,REFERENCE
,ACCOUNT_DESC
,CONVERT(NVARCHAR(8), ACCOUNT) AS AcctSort
FROM #nisse2
ORDER BY COMPANY
,ACCOUNT
,SUB_ACCOUNT
,ACCT_UNIT
,FISCAL_YEAR
,TMONTH
,POSTINGDATE
GRANT EXEC ON TransHistoryAccount20091016_pr TO public
GO
October 28, 2009 at 11:33 am
I really don't see any reason that you need the grant statement as part of the Stored procedure execution code. You can put it into a header comment so that it is already there and the person who creates the Stored procedure can just highlight it/execute it from the header after he creates the SP. I do this on some of my Stored procedures.
Putting the grant in the stored procedure itself makes zero sense. The grant could never be executed by the person it is targeting because they can't execute it in the first place.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply