Re: Where the !@?!@ is my Stored Procedure?

Crazy Cat (danbredy@xxxxxxxxxxx) writes:
> Responding to my own post - I created a store procedure and deleted it
> then couldn't locate it. I guess if you don't specify the database and
> owner, by default the stored procedure is copied into the Master
> database under system stored procedures (!), so if you can't find your
> stored procedure, this is probably a good place to look.
>
> Still perplexed about why users are allowed to store stuff in the
> System Procedure folder and why this is where procedures with
> unqualified names are stored by default.

First or all, there is no System Procedure folder, really. That is
just a visualization in Mgmt Studio.

Users are indeed permitted to create objects in the master database
if they have permissions. And, assuming that you have SQL Express on
your own machine, you presumably belong to BUILTIN\Administrators on
the box, and thus you have sysadmin when you log in with Windows
Authentication. And then you may indeed create procedures in the master
database.

Mgmt Studio has a dropdown which tells you the current database, and
this also displays on the tab, although it may be compressed. The Object
Explorer is not related to the query window.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: Alternative to Dynamic SQL?
    ... We have a single user per database. ... You use SQL Server logins for security as opposed to Integrated Security? ... DBA....and Tony was the developer and Tony wanted a pagination query, ... You can use a stored procedure to paginate database-side as well, ...
    (microsoft.public.sqlserver.programming)
  • Re: using sp_ as a naming convention for stored procedures
    ... System stored procedures are created and stored in the master ... database and have the sp_ prefix. ... from any database without having to qualify the stored procedure name fully ... SQL Server always looks for a stored procedure ...
    (microsoft.public.sqlserver.programming)
  • Re: Debug stored procedures with VB6
    ... > I can't see in the sql server analyser a tool to debug a stored procedure. ... > "Val Mazur" a écrit dans le message de ... >>>>> My database is installed locally. ...
    (microsoft.public.vb.database.ado)
  • RE: ASP.NET/Linked Server connection problem
    ... Destroy security and open the database for hackers by reducing security ... Wrap your work in a stored procedure that your connecting user account ... Create a custom ETL application to move the data. ... > I am trying to create/use a SQL Server Linked Server definition from ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Facing the same problem
    ... My stored procedure is being activated from a Service Broker ... remote server is denied because the current security context is not ... The linked server is a SQL server, but not one where I have any ... ALTER DATABASE db SET TRUSTWORTHY ...
    (microsoft.public.sqlserver.security)