SQL Server project - how to publish stored procedure permissions-CodePudding

In my .NET WinForms application (in a Visual Studio solution), I have the tables and stored procedures in a SQL Server project within the solution so I can easily keep my schema under version control, and I can successfully use the 'Publish' feature to deploy schema changes to the development database.

I'm getting ready to deploy my application and have asked a user to trial the application on their PC against the development database prior to rolling out the new application and database schema changes to production company wide.

What I'm finding is that the application is throwing SqlException. I've managed to track this down to permissions on the new stored procedures (obviously, I don't have this issue as the owner of the stored procedures).

I can manually correct this by granting permissions on the stored procedure(s), as follows

GRANT EXECUTE ON [dbo].[<tablename(s)>] TO DatabaseUsers

...but what I'd ideally like to do is include this within the definition of the stored procedure(s) in the SQL Server project that's under version control.

I've tried adding the above statement to the end of the stored procedure definition (below) in the SQL Server project, the output from the deployment script seems to show the command being executed, however whilst it updates the stored procedure, it won't touch the permissions.

    -- Snipped 50 lines above for brevity
    OR              c.name LIKE @search
    OR              CAST(it.id AS VARCHAR) LIKE @search
    OR              ig.name LIKE @search
    ORDER BY        it.id
END

GRANT EXECUTE ON [dbo].[search_items_allfields] TO DatabaseUsers
GO

I've also tried adding an additional GO before the GRANT statement in the above definition, but then I'm unable to use the publish script, as it refuses to run due to not being able to resolve the group 'DatabaseUsers' (without the GO, it's still unable to resolve it, but is happy enough to run it).

CodePudding user response：

In addition to the GO before the GRANT (so it's not part of the procedure), you need to add a script for the role to your project to resolve the reference:

CREATE ROLE DatabaseUsers;
GO

Of course, you'll need to add role members too. I suggest you manage role memberships separately rather than part of the SSDT project since those will vary by environment and many organizations have separate process for managing database access security.