CODEWITHSUNDEEP
sqlstored-proceduressql-server-2008-r2global
mattgcon
mattgcon

Reputation: 4858

Possible to create a SQL stored procedure for use for all databases

I have a stored procedure, in one database within my SQL server, that sets permissions to all stored procedures at once for that particulat database. Is there a way to create this stored procedure in a way were I can call it easily from any database within the SQL server and if so how do I go about doing such a thing

Upvotes: 1

Views: 3510

Answers (3)

Rhumborl
Rhumborl

Reputation: 16609

While the best solution to this specific question of granting execute to all procedures is the one provided by marc_s, the actual question was is there a way to create a single stored procedure and make it available to all databases.

The way to do this is documented at https://nickstips.wordpress.com/2010/10/18/sql-making-a-stored-procedure-available-to-all-databases/:

  1. Create the stored procedure in the master database.
  2. It must be named to start with sp_, e.g. sp_MyCustomProcedure
  3. Execute sys.sp_MS_marksystemobject passing the name of the procedure, e.g. EXEC sys.sp_MS_marksystemobject sp_MyCustomProcedure

Here is a simple example which just selects the name of the current database:

use master
go

create procedure dbo.sp_SelectCurrentDatabaseName as begin
    select db_name()
end
go

execute sys.sp_MS_marksystemobject sp_SelectCurrentDatabaseName
go

Calling exec dbo.sp_SelectCurrentDatabaseName on any database will then work.

To mark the procedure as not a system object, there a some nasty hacks suggested at https://social.msdn.microsoft.com/Forums/sqlserver/en-US/793d0add-6fd9-43ea-88aa-c0b3b89b8d70/how-do-i-undo-spmsmarksystemobject?forum=sqltools but it is safest and easiest to just drop and re-create the procedure.

Caveat

Of course creating system procedures like this is breaking the common rule of not naming your own procedures as sp_xxx, due to the possibility of them conflicting with built-in procedures in future versions of SQL Server. Therefore this should be done with care and not just create a load of randomly named procedures which you find useful.

A common simple way to avoid this is to add your own company/personal prefix to the procedure which Microsoft is unlikely to use, e.g. sp_MyCompany_MyCustomProcedure.

Upvotes: 3

marc_s
marc_s

Reputation: 755083

I have a stored procedure, in one database within my SQL server, that sets permissions to all stored procedures at once for that particular database.

You could archive the same result much easier:

  • create a new role, e.g. db_executor

    CREATE ROLE db_executor

  • grant that role execute permissions without specifying any objects:

    GRANT EXECUTE TO db_executor

This role now has execute permissions on all stored procedures and functions - and it will even get the same permissions for any future stored procedure that you add to your database!

Just assign this role to the users you need and you're done....

Upvotes: 0

AaronLS
AaronLS

Reputation: 38374

Have you tried a 3 or 4 part name?

InstanceName.DatabaseName.dbo.usp_Name

That procedure could in turn reference objects in other databases using the same conventions. So you could parameterize the name of the database to be operated on and use dynamic SQL to generate 4 part names to reference objects such as system tables.

Upvotes: 0

Related Questions