How to grant such a permission?

Question

How to grant such a permission?

SQL ORACLE

One Orange Chip

Points: 27807
More actions
February 26, 2008 at 1:09 pm

#203678

Here is the scenario.
A user is a database owner, but has no access at all to master database. Now, the user would like to execute one stored procedure in master database. We only want to grant the execute permission to the user, but does not want to grant anything else, such as public.
What is the best way to do it?
Any input will be greatly appreciated.

Viewing 12 posts - 1 through 11 (of 11 total)

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

Adam Bean One Orange Chip Points: 26631 More actions · Answer 1

SQL ORACLE (2/26/2008)
Here is the scenario.
A user is a database owner, but has no access at all to master database. Now, the user would like to execute one stored procedure in master database. We only want to grant the execute permission to the user, but does not want to grant anything else, such as public.
What is the best way to do it?
Any input will be greatly appreciated.

GRANT EXECUTE ON [ObjectName] TO [User]

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 2

It is correct in syntax, but the user must be created in master database.

As described in my question, the user does not have any other access to master database. We do not want to this account to hold public permission to view anything in master database.

Adam Bean One Orange Chip Points: 26631 More actions · Answer 3

Make a custom role in the database, grant execute to the role, and then give the user access to that role.

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 4

SQL ORACLE (2/26/2008)
Here is the scenario.
A user is a database owner, but has no access at all to master database. Now, the user would like to execute one stored procedure in master database. We only want to grant the execute permission to the user, but does not want to grant anything else, such as public.
What is the best way to do it?
Any input will be greatly appreciated.

Is this a system sproc, originally found in master, or a custom-made sproc?

If the latter, may I suggest you create it in model db instead? master db is generally not a good place for user code.

See:

http://www.sqlservercentral.com/Forums/Topic457280-149-1.aspx

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

Ummm... neither is Model...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 6

No don't create it in the model database as this acts as a template for user DBs to be created and your procedure will be created in all your new databases then. check if its a system procedure and then give necessary permissions only to master database.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 7

Sugesh Kumar (2/28/2008)
No don't create it in the model database as this acts as a template for user DBs to be created and your procedure will be created in all your new databases then. check if its a system procedure and then give necessary permissions only to master database.

Sugesh, you are right, I hadn't thought this through carefully enough.

It looks like one cannot avoid defining a generic custom object in at least one user database in every instance, and then have a way of referring to it from every other database, either as db_name.schema_name.object_name or as some kind of SYNONYM equivalent.

In the past I have placed such objects in master, but I have learned recently that this may not be a good idea.

Can people give some feedback as to why master is not a good choice?

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

The problem is, that YOU don't own Master... Microsoft does. They can put or remove anything they like, including code that doesn't belong to them, during any service pack or hot fix. Futher, unless you're really careful about what you name things, you could actually prevent a hot fix or sp from being installed or MS could just overwrite your stuff without any notification that it did.

At the very least, you need to very that all YOUR stuff is still in Master after every hot fix or sp and that the hot fix or sp actually works.

Think of it this way... if you make a change to your car's engine, you void the warranty...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 9

Why does it have to be in master?

You cannot add the user to master without them being a member of public. That's a fundamental construct in SQL Server.

You can have a proc in another database that references objects in master or you can context switch (EXECUTE AS) to avoid giving rights, but you cannot add a user to master without them being a part of public.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 10

Actually, you hit the nail on the head, Steve... there should be a database for utilitlity functions of this nature. I suggest the name of UTILITY? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 11

Steve Jones - Editor (3/2/2008)
You cannot add the user to master without them being a member of public.

Steve,

Sorry, I'm missing the point, can you elaborate? What are the implications of this?

Is it that such a user would be given unwarranted access to other resources in master?

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]