All My Stored Procedures Are Getting Created As System Procedures!

Nov 6, 2007



Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.

For example, even this simple little guy:

CREATE PROCEDURE BOB

AS

PRINT 'BOB'

GO

Gets created as a system stored procedure.

Any ideas what would cause that and/or how to fix it?

Thanks,
Jason

View 16 Replies


ADVERTISEMENT

Stored Procedures Getting Created As System

Nov 8, 2007

I need to create a stored procedure in the master database (yes, I know it's not that good of an idea). I'm working with SQL 2K5, SP2 Whenever I create it, it is marked as a system stored procedure no matter what I name it, what schema I put it in, or what user I use to create it (sysadmin or minimal permissions).

As soon as I create it, if I do any of the following, I can see it to be a system stored procedure and not a regular user sp.

1) SELECT * FROM sys.objects where is_ms_shipped = 1
2) SELECT * FROM sys.procedures where is_ms_shipped = 1
3) Looking in SSMS... There is a special folder for system stored procedures in SSMS, and mine is in there.

At least in my case, the only thing it hurts is that you have to be a sysadmin to execute that stored procedure (and I need to have a non sysadmin be able to execute it). Other than that, it executes normally when run by a sysadmin.

Any suggestions on why this is happening? It's only happening on 1 out of about 80 SQL servers we have.

View 4 Replies View Related

About Default Created System Stored Procedures

May 12, 2008

i found that some stored procedure get created automatically in sqlserver 2000 (system stored procedures) ,while doing my work i accidentally deleted those stored procedures can any body answer following questions1: why these stored procedures are there and automatically created2: what happen en if i deleted those stored procedures3: how to recreate those stored procedures with limited user permission thanks in advance 

View 2 Replies View Related

Can't Find My Stored Procedures I've Created

Jun 24, 2007

Hi
im using sqlserver studio managment
i goto a specific database and goto stored procedures and add stored procedures from context menu and created successfully .

i try to find it between procedures but not found i try to create it again gives me error that already found so.
where i can get my created stored procedures from sqlserver studio managment?

thanks in advance.

View 1 Replies View Related

Temp Tables Created In Stored Procedures

Feb 12, 2008

Are they unique to a user/session? Like if 2 users simultaneously run the stored procedure?
TIA!

View 13 Replies View Related

Reporting Services :: How To Find Out Last Created Stored Procedures

Jun 8, 2015

How to find out last created stored procedures in sql.

View 5 Replies View Related

Stored Procedures - Using Return Value From Dynamically Created SQL Statement

Jul 29, 2007

Hello,

This is my foray into Stored procedures, so I'm hoping this is a fairly basic question.

I'm writing a stored procedure, in which I dynamically create an SQL statement.
At the end of this, the SQL statement reads like:



Code SnippetSELECT COUNT(*) FROM StockLedger WHERE StockCode = 'STOCK1' AND IsOpen = 1 AND SizeCode = 'L' AND ColourCode = 'RED' AND LocationCode IS NULL AND RemainingQty > 0



Now this statement works a charm, and returns a single value. I want to assign this count to a variable, and use it further on in the stored procedure. This is where the problems start - I cant seem to do it.

If I hard code a statement, like



Code SnippetSELECT @LineCount = COUNT(*) FROM StockLedger
that works fine (although it brings back a count of all the lines).

But if I modify the dynamically created SQL Statement from earlier on to:



Code SnippetSELECT @LineCount = COUNT(*) FROM StockLedger WHERE StockCode = 'STOCK1' AND IsOpen = 1 AND SizeCode = 'L' AND ColourCode = 'RED' AND LocationCode IS NULL AND RemainingQty > 0
it doesnt work - it complains: Must declare the scalar variable "@LineCount".

Just to clarify, when I say "dynamically created an SQL statement, I mean that by a bunch of conditional statements I populate a varchar variable with the statement, and then eventually run it exec(@SQLStatementString)

So, my question would be, how do I do this? How do I make a dynamically generated SQL statement return a value to a variable?


View 3 Replies View Related

SQL Server 7.0 System Stored Procedures

May 1, 2001

About a year ago we inherited a SQL server (7.0) from another division of our company. The time has come to migrate the SQL Server functionality onto a new machine. Before I do this though, I need to figure out what the initial installation configuration was so I can set up the new server with the same specs. I am looking for a system stored procedure(s) that can tell me the following:

Case sensitivity, accent sensitivity, SQL build (SP), character set, etc. Basically I need to know what checkboxes were ticked during the initial set up of the server.

Any help would be greatly appreciated. TIA.

View 2 Replies View Related

Model Db Got No System Stored Procedures

Nov 7, 2001

So every database i created has no system stored procedures. I didn't know how that could happen as I am not the person who installed sql2000. What should I do to get all the system stored procedures back?

TIA

View 1 Replies View Related

Modify System Stored Procedures

Oct 31, 2002

I am trying to edit a system stored(sp_add_dtspackage) procedure and cannot for the life of me find where to edit. This procedure does a check to keep users from saving changes to a package they do not own. I need users to be able to change the the packages when they are not always the one who created.

View 2 Replies View Related

Missing System Stored Procedures

Aug 28, 2002

I have a SQL Server installation that is missing the stored procedures used for mail (xp_startmail, xp_stopmail, etc.). Is there a way that I can put these on the server without having to reinstall SQL? I tried to generate them from other servers but SQL won't allow you to do this with these particular stored procedures.

View 1 Replies View Related

Missing System Stored Procedures

Feb 25, 2005

When I create a new database, the system stored procedures are missing;

dt_addtosourcecontrol_u
dt_checkinobject_u
etc.

I'm logged in as sa.

View 5 Replies View Related

System Stored Procedures Do Not Execute

Oct 23, 2006

I tried this morning to check some of the system stored procedures and ran into trouble. Only four of them executed: sp_alterdiagram, sp_creatediagram, sp_dropdiagram and sp_helpdiagramdefinition. I could not check all the rest, I did it selectively. The typical error message was: Invalid object on RETURN statement. Some had syntactical errors.
sp_ActiveDirectory_Obj
Invalid object name 'sys.sp_ActiveDirectory_SCP' Line 171
sp_ActiveDirectory_SCP
Invalid object name 'sys.sp_ActiveDirectory_SCP' Line 171
sp_ActiveDirectory_Start
Invalid object name 'sys.sp_ActiveDirectory_Start' Line 19
Invalid object name 'sys.sp_add_agent_parameter' Line 60
Invalid object name 'sys.sp_add_agent_profile' Line 123
Invalid object name 'sys.sp_add_data_file_recover_suspect_db' Line 17
sp_addalias
Msg 102, Level 15, State 1, Procedure sp_addalias, Line 44
Incorrect syntax near '%'.
Msg 195, Level 15, State 10, Procedure sp_addalias, Line 64
'get_sid' is not a recognized built-in function name.
Msg 102, Level 15, State 1, Procedure sp_addalias, Line 78
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_addalias, Line 119
Incorrect syntax near '%'.
sp_bindefault
Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 95
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 134
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 182
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 208
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 228
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 264
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 273
Incorrect syntax near '%'.
sp_databases
Invalid object name 'sys.sp_databases'. Line 6
sp_tables
Invalid object name 'sys.sp_tables'

Is there a chance that these errors are due to the fact that I executed them without parameters?

Thanks

View 2 Replies View Related

Undocumented System Stored Procedures

May 15, 2007

I know there are a lot of undocumented system stored procedures such as xp_ntsec_enumdomains, xp_instance_regread etc, that exist on SQL Server.



Does anyone know of any good websites that contain descriptions of what these stored procedures do? In particular I know that a default installation of SQL Server 2000 leaves permission to execute many of these granted to public and I am interested in finding out what the implications of each one of these are? I have tried Googling this topic but there doesn't seem to me much or there (or what is there is in Chinese and I don't really want to click on to!)



Once again thanks for the help.

View 3 Replies View Related

System Stored Procedures/behavior Not Understood

May 11, 2004

I recall that stored procedures created in the master database and having a name beginning with sp_ are available from all databases. However, I've found varying results when invoking such a procedure from a different database. For example:

DECLARE tables CURSOR FOR
select name from sysobjects
where type='U'
order by name
for read only

populates a cursor with the list of tables in the database from which you are running the procedure. However this query in the same stored procedure:

SELECT count(*)
FROM sysExcludeMaint
WHERE @tablename like tablename

fails with "Invalid object name 'sysExcludeMaint'." if the table sysExcludeMaint
doesn't exist in the master database (it does exist in the database from which I've invoked the proc). I'm not clear on why the different results. Anyone know?

View 2 Replies View Related

Recreating/reloading System Stored Procedures

Jul 21, 2006

A system stored procedure got accidentally deleted, and all backups aresince the stored procedure was deleted (wonderful!)Can the SQL for the stored procedure be extracted from another serverand loaded as opposed to removing everything and then rebuilding theserver?Thanks in advance!

View 1 Replies View Related

Export/import A Non System Stored Procedures From One DB To Another

Aug 23, 2006

Hello, I want to export/import a non system stored procedures from one DB to another. How Should I proceed ?

Thanks in advance.

View 1 Replies View Related

How To Save Stored Procedure To NON System Stored Procedures - Or My Database

May 13, 2008

Greetings:

I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.

How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?

Thanks!

View 5 Replies View Related

How To Implement The Replication With The System Stored Procedures Or TransactSQL?

Jun 8, 2004

how to implement the replication with the System Stored Procedures or the Transact-SQL ?

i want to encapsula my replication and make it be reusable and ease to use.So i want to do the replication in a script file.how can i do this?

Thanks.

View 9 Replies View Related

Replication System Stored Procedures Parameter Defaults ?

Oct 27, 2005

Hi there

View 5 Replies View Related

What System Stored Procedures Have Changed Between SQL 2000 And SQL 2005?

Aug 2, 2006

What system stored procedures have changed between SQL 2000 and SQL 2005?

View 4 Replies View Related

Merge Agent System Stored Procedures And Sp_Recompile

Jul 13, 2006

Hello,

We are trying to be proactive and stop a potential performance issue by reducing the number of recompiles in our SQL 2000 database application. This database is replicated. After viewing output from Profiler and PerfMon it seems that over 90% of the recompiles are due to system stored procedures generated by replication merge agents. Can anything be done about this?

Thanks

View 3 Replies View Related

Stored Procedure Being Saved In System Stored Procedures

Apr 7, 2006

We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.

For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.

I can't update the sys.Objects or sys.Procedures views in 2005.

What effect will this flag (is_ms_shipped = 1) have on my stored procedures?

Can I move these out of "System Stored Procedures" and into "Stored Procedures"?

Thanks!

View 24 Replies View Related

SQL Security :: How To Disable User Access To The Critical And System Stored Procedures

Sep 22, 2015

disable the following system stored procedure (xp_grantlogin and xp_revokelogin)?

View 5 Replies View Related

Is It Possible To Change Permissions Of SQL Server System Stored Procedures And DBCC Commands?

May 8, 2008

Hi all,

I would like to enable users that do not belong to groups (server roles) such as sysadmin, serveradmin and don't have db permissions such as ddl_admin or db_owner to run some of the system stored procedures (such as sp_addumpdevice sp_configure sp_serveroption ...) and DBCC commands (such as DBCC CHECKFILEGROUP - requires ob_owner or sysadmin permission).

Is it possible to change permissions of SQL Server system stored procedures?

Is it possible to change permissions of SQL Server DBCC commands?

Thanks,

Assaf

View 1 Replies View Related

Oracle Stored Procedures VERSUS SQL Server Stored Procedures

Jul 23, 2005

I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!

View 11 Replies View Related

Stored Procedures 2005 Vs Stored Procedures 2000

Sep 30, 2006

Hi,



This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.



Thank you in advance for any help on this matter



View 1 Replies View Related

Viewing Created Procedures And Functions

Sep 11, 2006

I'am using SqlServer 2005..

I'am able to view created tables via:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES;

How do I do the same with created procedures and functions? i.e how do I view all procedures and functions using SELECT query?

View 2 Replies View Related

System Procedures

May 2, 2007

Hi all

as like there is a query in oracle as
select * from tab

we get all tables

Like wise is their any query in sql server 2005
b'coz

I want all system procedures
as like sp_password or sp_help
i want list of all procedures

for that i wrote query as

select * from sysobjects
but in output i don't find any name starting with sp_

Thanks in advance


Malathi Rao

View 20 Replies View Related

Imp System Procedures

Mar 16, 2008

hi, i see there are lots of system store procedures available but could you specify me the ones that are used in day to day work as a DBA??

View 6 Replies View Related

Undocumented System Procedures

May 21, 2015

I am trying to determine which are undocumented system procedures used to satisfy our company's security audit. Well, the nature of undocumented system procedures is it's undocumented. Hence, it's hard to find.

So, is there a way I can get a list of SQL Server documented system procedures?

View 10 Replies View Related

SQL 2005 System UDF Function And Procedures

Jul 15, 2005

In SQL 2000 for Procedures

View 5 Replies View Related

Why User Defined Procedure Shows Under System Procedures In Object Explorer

Jan 17, 2008

I have created a store procedure in sql server 2005.
At first, it shows up as a normal user defined store procedure in Object explorer.

Recently, I found I could not found the procedure in normal place. But the execution is fine.
Finally I found it stays under system procedure folder.


How this could happen?

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved