Busy scription stored procedures and database role permissions

This post provides an alternative script for exporting stored procedures with their dependencies. All you need to do is providing the role name, the destination database name and the destination username.

Techi Info

I could have made the script way smaller using less cursors. But keeping the script on such a granular level will help you understanding and customizing it.

DECLARE @sSourceRoleName nvarchar(75)  
DECLARE @sDestinationUser nvarchar(75)  
DECLARE @sDestinationDBName nvarchar(50)


SET @sSourceRoleName = 'demoRole'  
SET @sDestinationUser = 'myUser'  
SET @sDestinationDBName = 'SCRIPT_TEST'  
------------------------------------------------------------------------
--hide the numbers
SET NOCOUNT ON  
----- Declarations
DECLARE @sSPName NVARCHAR(150)  
DECLARE @sCurrentLine NVARCHAR(150)  
--create temp tables to store the SPs
CREATE TABLE #tSPNames ( sSPName nvarchar(150), bDirect bit)  
CREATE TABLE #tSPData ( sSPLines nvarchar(250))  
-----------------------------------------------get the stored procedures to extract----------------------------------------
--insert the sps into the temp table
INSERT INTO #tSPNames  
SELECT DISTINCT spname.name, 1  
FROM   sys.database_principals prinicipals  
INNER JOIN sys.database_permissions db_perm ON db_perm.grantee_principal_id = prinicipals.principal_id  
LEFT JOIN sys.objects spname ON db_perm.[major_id] = spname.[object_id]  
WHERE  prinicipals.name = @sSourceRoleName  
AND spname.type = 'P' -- limit to stored procedures  
UNION  
SELECT DISTINCT referenced_entity_name, 0  
FROM sys.sql_expression_dependencies AS sed  
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id  
WHERE referencing_id IN (SELECT DISTINCT OBJECT_ID(spname.name)  
                        FROM   sys.database_principals prinicipals 
                        INNER JOIN sys.database_permissions db_perm ON db_perm.grantee_principal_id = prinicipals.principal_id 
                        LEFT JOIN sys.objects spname ON db_perm.[major_id] = spname.[object_id] 
                        WHERE  prinicipals.name = @sSourceRoleName
                        AND spname.type = 'P') -- limit to stored procedures

-- *********************************************************************************************************************
--------------------------------------------- get the stored procedures content----------------------------------------
DECLARE c_spcursor CURSOR FOR  
SELECT sSPName  
FROM #tSPNames

OPEN c_spcursor  
FETCH NEXT FROM c_spcursor INTO @sSPName 

WHILE @@FETCH_STATUS = 0  
BEGIN  
    --storing the stored procedure content in a table
    INSERT INTO #tSPData
    exec sp_helptext @sSPName

    --insert new line and spaces
    INSERT INTO #tSPData
    SELECT 'GO'
    UNION
    SELECT ''
    UNION
    SELECT ''
    FETCH NEXT FROM c_spcursor INTO @sSPName  
END  

CLOSE c_spcursor  
DEALLOCATE c_spcursor  
-- *********************************************************************************************************************
PRINT 'USE '+@sDestinationDBName  
PRINT 'GO'  
PRINT ''  
-------------------------------------------- Produce the sp output----------------------------------------
DECLARE c_spcursor CURSOR FOR  
SELECT sSPLines  
FROM   #tSPData

OPEN c_spcursor  
FETCH NEXT FROM c_spcursor INTO @sCurrentLine 

WHILE @@FETCH_STATUS = 0  
BEGIN  
    PRINT @sCurrentLine 
    FETCH NEXT FROM c_spcursor INTO @sCurrentLine  
END  

CLOSE c_spcursor  
DEALLOCATE c_spcursor  
-- *********************************************************************************************************************
-------------------------------------------- Add the sp permissions----------------------------------------
PRINT 'CREATE ROLE '+@sSourceRoleName+' AUTHORIZATION '+@sDestinationUser+';'

DECLARE c_spcursor CURSOR FOR  
SELECT sSPName  
FROM #tSPNames  
WHERE bDirect = 1

OPEN c_spcursor  
FETCH NEXT FROM c_spcursor INTO @sSPName 

WHILE @@FETCH_STATUS = 0  
BEGIN  
    --storing the stored procedure content in a table
    PRINT 'GRANT EXECUTE ON '+@sSPName+ ' TO '+@sSourceRoleName
    PRINT 'GO'
    FETCH NEXT FROM c_spcursor INTO @sSPName  
END  

CLOSE c_spcursor  
DEALLOCATE c_spcursor  
-- *********************************************************************************************************************
--Cleanup
DROP TABLE #tSPData  
DROP TABLE #tSPNames

This kept me busy for: 60 minutes

Up next: Connecting from C# Windows App to SQL Azure

References
Getting the stored procedures linked to database role: http://dba.stackexchange.com/questions/36618/list-all-permissions-for-a-given-role by elgabito
Creating a cursor: https://www.mssqltips.com/sqlservertip/1599/sql-server-cursor-example/
Getting the sql dependencies: https://msdn.microsoft.com/en-us/library/ms345404.aspx

Menu