System stored procedure

Question

System stored procedure

ouertani neked

SSC-Addicted

Points: 406
More actions
April 15, 2004 at 5:48 am

#86565

Hi every body, something was changed in my sql options, each time I create a new database either using the assistant or Transact sql it doesn't have any system stored procedure (sp_...) inside!!
thanks for your help
cheers

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

I think when you create a new DB, SQL Server makes a copy of your model DB. Everything ok with that DB?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

ouertani neked SSC-Addicted Points: 406 More actions · Answer 2

Thank you Frank, in fact there is no SP in my model DB, is exporting them from an existing DB to the mode lDB?

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Oh sh*t, never type while being unconcentrated.

There are no s_Procs in a model db anyway. What a bad post I made!

Apologize for irritating you!

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

So, now is more time.

Why do you expect system procedures to be in a new db?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

ouertani neked SSC-Addicted Points: 406 More actions · Answer 5

Hi, if i have to use any of the system SP in my SPs without having them in my DB (from the mastr DB), do i risk to have any problem when I deploy my application for the client (msde), otherwise it's fine.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Not the best example, but

CREATE DATABASE TEST_ME

GO

USE TEST_ME

GO

CREATE PROCEDURE Test_P

AS

EXEC SP_TABLES

GO

EXEC Test_P

should return 21 rows, that is 19 system table entries and two views. That is the default for a new database unless you haven't manipulated your model db.

When you look at this db, you still won't see any system procedures. However, you obviously can use them.

HTH

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Mark Shepherd SSC Enthusiast Points: 172 More actions · Answer 7

There are no system stored procedures in a newly created database. Only system tables and views.

If you want to run system stored procedures (master or msdb) from your database you may run into permission problems.

Make sure the user in your database is a user in the master database with permissions only to the sp's you need. Make sure the database owners are the same, full qualify calls to the system sp's, make sure all your sp's that call system sp's are dbo owned and make sure database ownership chaining is turned on for your database.