Re: Stored procedure loses permissions daily

From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 04/16/04 

Date: Fri, 16 Apr 2004 15:10:16 -0400

To check if this is happening, check the value of crdate for the proc in
sysobjects. If it is recent, then the object is being dropped and
recreated. 

-- 
Tom
---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Geoff N. Hiten" <SRDBA@Careerbuilder.com> wrote in message
news:%23FBXaS%23IEHA.3220@TK2MSFTNGP12.phx.gbl...
Sounds like the procedure is getting dropped and recreated.  The creation
script likely doesn't have the GRANT statements included.
-- 
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"muddtrav" <anonymous@discussions.microsoft.com> wrote in message
news:F22FFD72-074D-488C-A80E-EAA557205903@microsoft.com...
> I have a stored procedure in a SQL 2000 database that loses all of the
explicit permissions on a daily basis. I have it set up to grant permission
to a single user account, but it keeps getting "unchecked".  The stored
procedure is used by a web page, and I continually have to change the
permissions back to what it should be  -- very frustrating.  Every other
object in the database keeps its permissions except this one.  Has anyone
seen this problem before???

Relevant Pages

  • ADP, Application Role, and objects
    ... The above link is to an atricle on how to implement SQL Server Application ... After you connect with your ADP, fire a bit of code to set the ... third party tools to view the data on the same database. ... Scenario 1 - If I explicitly grant permissions on that object to the user ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Deny access to all users (including Administrator and DomainAd
    ... permissions on stored procedure override ... applictaion's user account permissions to the stored procedure which will ... Will try to deny access through the query for SQL Server 2005 and 2000. ... Remove everyone that you don't want rom an Administrator Groups ...
    (microsoft.public.sqlserver.security)
  • Re: Execute Persmission denied on object sp_OACreate
    ... > SQL Server is creating a job behind the scenes. ... > permissions. ... > SA account password and gaining access to the database. ... >>> How can get a user permissions to execute these stored procedures ...
    (microsoft.public.sqlserver.security)
  • Re: Newbie: I dont understand user permissions for table access
    ... > My database is remote to my workstation. ... > tables/fields WITHOUT specifying anything in the permissions dialogs? ... >> HOW are you connecting to SQL Server? ... what rights/permissions have been granted to the PUBLIC role? ...
    (microsoft.public.sqlserver.server)
  • Re: Execute Persmission denied on object sp_OACreate
    ... SQL Server is creating a job behind the scenes. ... SA account password and gaining access to the database. ... > SQL Server doesn't check permissions on indirectly referenced objects as ... > the proxy account security context for non-sysadmin users from Enterprise ...
    (microsoft.public.sqlserver.security)