EXEC store proc in master DB

Question

EXEC store proc in master DB

Mike Levan

SSChampion

Points: 14201
More actions
November 16, 2008 at 12:36 pm

#124854

I created a store proc in master dataabse with prefix "sp_" but when a DB Role which is not an admin tries running that proc it gives permission error. For the role i have granted EXEC permissions for that store proc but still i get permissions error, is there anything else i have to do with regard to permission.

Viewing 15 posts - 1 through 14 (of 14 total)

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

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 1

Depends if the sp needs to have system admin rights which sounds like it does.

If the user needs to be able to run this and it is something like alter login then the rights need to be systems admin for this user. If you don't want to give these rights out your have to look into certificates.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Depends. What does the proc do?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 736445 More actions · Answer 3

Steve Jones - SSC Editor

SSC Guru

Points: 736445

November 16, 2008 at 8:44 pm

#899141

Are you trying to get users to run it from another DB?

Mike Levan SSChampion Points: 14201 More actions · Answer 4

I am trying to get users to run it from any DB in the instance to check the Lock Details.

Also how can i test that proc by running as a regular user because my windows login has a sysadmin previlages.

GabyYYZ SSCertifiable Points: 7913 More actions · Answer 5

After you create the script, run this command from within master:

exec sys.sp_MS_marksystemobject 'sp_YOUR_SCRIPT_HERE'

which allows you to run it from any database.

Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein

Mike Levan SSChampion Points: 14201 More actions · Answer 6

I tried that but still didnt work.

If am doing os for making it sys proc, can i delete it later, does it impact anything by delete.

Mike Levan SSChampion Points: 14201 More actions · Answer 7

I am just looking for a way where a regular user can execute sp_Lock system store procedure.

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 8

Need to look into certificates this way you can allow the user to do this.

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 9

Does the average user get what sp_lock is telling them, why don't you just do an alert with email to the user instead that there is a lock, also what are they going to do with this information.

Mike Levan SSChampion Points: 14201 More actions · Answer 10

what do yo mean by alert through email, how is it possible. my developers whats to look at locks and who is doing what as that group will be kicking jobs with huge amounts of data flowing in and they want make sure their jobs run OK when the resources are free.

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 11

Developers shouldn't be on a production database, if it is a test database then i would give them sa rights...after all it is a development system.

If you don't want to do this then your need to use certificates.

Look at this.

What it does it prevents you from giving sa rights to the user - so your create a sp with say

sp_lock in it and then your assign the certificate and then grant this certifiate to your user.

So basically the user can run your sp but thats all they can do on the server.

It gets a little complex with certificates but i have used it on my server.

http://blogs.msdn.com/lcris/archive/2005/06/15/sql-server-2005-procedure-signing-demo.aspx.

homebrew01 SSC Guru Points: 55548 More actions · Answer 12

Mike Levan (11/17/2008)
I am trying to get users to run it from any DB in the instance to check the Lock Details.
Also how can i test that proc by running as a regular user because my windows login has a sysadmin previlages.

I create a 2nd Windows login for myself with no special rights, then I use that for testing as if I was a regular user.

Mike Levan (11/16/2008)
I created a store proc in master dataabse with prefix "sp_" but when a DB Role which is not an admin tries running that proc it gives permission error. For the role i have granted EXEC permissions for that store proc but still i get permissions error, is there anything else i have to do with regard to permission.

Isn't it better to us a different prefix than 'sp_' when creating your own stored procs ?? Then they won't get confused with system stored procs during upgrades or migrations.

Mike Levan SSChampion Points: 14201 More actions · Answer 13

Isn't it better to us a different prefix than 'sp_' when creating your own stored procs ?? Then they won't get confused with system stored procs during upgrades or migrations.

But still i dont want to create my proc on each n every database. I want to create in master and run it in all db's

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 14

You wouldn't have to do this...You could have it in master no need to do in every database.

If you have a system database ..i.e SYSADMIN which is where your putting your custom sp etc..then u fire the stored procedure from here for the users it will look at all your locks

not just in the SYSADMIN one

You can do it all from master - but who puts custom sp in master ...that should be a no no.