Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jason@shadonix.com
Starting Member
3 Posts |
 Posted - 2007-11-08 : 15:19:19
|
| 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 = 12) SELECT * FROM sys.procedures where is_ms_shipped = 13) 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. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-08 : 15:25:19
|
| Please explain why you need it in master. I've got tons of DBA stored procedures that access the other databases and I have no need to put it into master. There are ways to connect to the other databases.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
jason@shadonix.com
Starting Member
3 Posts |
Posted - 2007-11-08 : 15:35:25
|
Political reasons mostly. There really isn't a good reason. As I said, I agree that it's not a good idea.quote:
Originally posted by tkizer
Please explain why you need it in master. I've got tons of DBA stored procedures that access the other databases and I have no need to put it into master. There are ways to connect to the other databases.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-08 : 15:43:46
|
| I just don't understand why you'd try to fix something like this when it doesn't need to be designed this way. I don't have an answer to your question as I never place objects in master.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
jason@shadonix.com
Starting Member
3 Posts |
Posted - 2007-11-08 : 15:59:25
|
I wish I was lucky enough to work in an environment where the DBA has the political power to say things like that and get people to listen. But I don't, as is the case for many. I have to figure out how to make it work.And, while it isn't best practice to put stored procedures in the master database (or any other object for that matter), it is perfectly legal to do so as far as SQL is concerned. They are supposed to be created as just regular user stored procedures as if they were in any other user database, but for some reason I have a server that isn't behaving that way. The strange thing is it's only one server, all the others are OK. And it only does it for SPs, user defined functions can be created normally.quote:
Originally posted by tkizer
I just don't understand why you'd try to fix something like this when it doesn't need to be designed this way. I don't have an answer to your question as I never place objects in master.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/
|
|
|
|
|
|
|
|
|
stored procedures getting created as system