Sajnos már többször megtörtént velem, hogy fejlesztés közben folyamatosan születnek az új tárolteljárások, és én nem készítek semmilyen szkriptet, amivel telepítéskor egy adott felhasználónak vagy adatbázis-szerepkörnek futtatási jogot tudnék adni ezekre a tárolteljárásokra. Ezt a futtatási jogot elég könnyen meg lehet adni pipálgatással, ha kevés tárolteljárásunk van. De mondjuk néhányszáz tárolteljárást már nincs kedvem végigpipálgatni. Ráadásul mi van ha elfelejtem. Meg egyáltalán szkriptekre szükség van!

A helyzet annyira tarthatatlanná vált, hogy mindenképpen készítenem kellett valamit, ami előállít nekem egy szkriptet, mely egy adott adatbázis összes tárolteljárására megadja a futtatási jogot valakinek.

Ehhez mindenképpen szükség volt valamire, amitől megtudhatom, hogy milyen tárolteljárások vannak egy adatbázisban. Némi keresgélés után a követekező SQL lekérdezést sikerült kiókumlálni:

SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES

Ez visszaadja az aktuális adatbázis összes hívható rutinját. Többek között a tárolteljárásokat is. Mivel nekem csak a tárolteljárásokra volt szükségem, ezért újabb keresgélés után így bővítettem a lekérdezést:

SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='procedure'

Ez már csak a tárolteljárásokat adja vissza. Sanos olyanokat is, melyeket a Microsoft (gondolom) valamilyen saját célra tesz az adatbázisainkba (köszönjük). Ezekre sem szeretnék futtatási jogokat osztogatni, ezért további kereskgélés után a követekező megoldásra találtam:

SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0
    AND ROUTINE_TYPE='procedure'

Szuper. Mostmár csak az kellene, hogy a tárolteljárások neve mellé azok sémáját is tudjam. És mondjuk legyen is összefűzve a sémanév a tárolteljárásnévvel, és legyen is kieszképelve (ezt vajon, hogy írják magyarul) az SQL [ és ] karaktereivel:

SELECT QUOTENAME(ROUTINE_SCHEMA) + '.' + QUOTENAME(ROUTINE_NAME) AS [Name]
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0
    AND ROUTINE_TYPE='procedure'

Ennek az SQL lekérdezésnek már egész használható eredménye van:

Olyan neveket kapunk vissza az adatbázistól, ami elé már csak oda kell írnunk, hogy GRANT EXECUTE ON, a végére meg hogy TO felhasznalo és készen is vagyunk. Erre legalább két lehetőségünk van: Vagy írunk egy fapados konzolalkalmazást ami kiírja a fenti sorokból álló szkriptet a konzolra, és ha kedvünk van inkább fájlba toljuk az eredményét, vagy írunk egy mégnagyobb SQL szkriptet ami előállítja a fenti sorokból álló másik szkriptet és le is futtatja azt. Utóbbi viszonylag bátor megoldás, csak a futás végén tudjuk megnézni, hogy mikre is adtunk mi futási jogot, míg az előbb említett esetben lesz egy állományunk, benne egy SQL szkripttel, amit úgy módosítunk ahogy nem szégyellünk és csak utána futtatunk.

Végül a fapados program mellett döntöttem:

class Program
{
    private const string ConnectionFormatter =
        "server={0};database={1};Trusted_Connection=true";

    private const string SelectString =
        "SELECT QUOTENAME(ROUTINE_SCHEMA) + '.' + QUOTENAME(ROUTINE_NAME) AS [Name] " +
        "FROM INFORMATION_SCHEMA.ROUTINES " +
        "WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0 " +
        "   AND ROUTINE_TYPE='procedure'";

    private const string CommandFormatter = "GRANT EXEC ON {0} TO {1}";

    private static void Usage()
    {
        string programName = Environment.GetCommandLineArgs()[ 0 ];
        Console.WriteLine( "{0} Server DataBaseName UserName", programName );
        Environment.Exit( 1 );
    }

    static void Main( string[] args )
    {
        if( args.Length != 3 )
            Usage();

        string connectionString =
            string.Format( ConnectionFormatter, args[ 0 ], args[ 1 ] );
        SqlConnection connection = new SqlConnection( connectionString );
        SqlCommand cmd = new SqlCommand( SelectString, connection );
        connection.Open();
        SqlDataReader dr = cmd.ExecuteReader();

        while( dr.Read() )
        {
            string spName = dr[ "Name" ].ToString();
            Console.WriteLine( CommandFormatter, spName, args[ 2 ] );
        }
        dr.Close();
        connection.Close();
    }
}

Tényleg elég fapados lett, sokmindent lehetne rajta még javítgatni (például, hogy ne csak Trusted Connectionnel menjen, vagy legyen benne hibakezelés). Viszont működik:

Egész pofásan visszaadja a GRANT EXEC sorokat. Ha a konzolkimenetet fájlba irányítom > jellel, akkor pedig kész az az SQL szkript fájl, amiről úgy álmodoztam.

Ma megpróbálkoztam a C# kódot nélkülöző teljesen SQL alapú verzióval is. Egyelőre itt tart:

DECLARE @user nvarchar( 255 )
SET @user = 'vedgrehajto'

SELECT 'grant exec on ' + QUOTENAME(ROUTINE_SCHEMA) +
    '.' + QUOTENAME(ROUTINE_NAME) + ' TO ' + @user
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0
    AND ROUTINE_TYPE='procedure'

A @userbe be kell settelni azt a felhasználó- vagy szerepkörnevet, akinek a jogot akarjuk adni, futtatni a queryt, és az eredményablakban már látszik is a szkriptünk. Ezt tudjuk vágólapon keresztül odamásolni, ahol szükségünk van rá.