Deploying Stored Procedures in Visual Studio - GRANT EXECUTE
-
Hi all. Is there a way to automatically grant execute permissions on CLR stored procedures upon deployment from a Visual Studio SqlServer project? I have a solution with a project for all my Sql Server stored procs, and a second project for a web site that tests them. When I rebuild/run the web site, the stored procedure assembly is rebuilt and re-deployed also (which is good) but because the stored procedures are recreated, the GRANT permissions previously on them are lost (which is not so good). I'd like to have the GRANT EXECUTE permissions added as an automatic part of the deployment process, if that is possible. Help?
-
Hi all. Is there a way to automatically grant execute permissions on CLR stored procedures upon deployment from a Visual Studio SqlServer project? I have a solution with a project for all my Sql Server stored procs, and a second project for a web site that tests them. When I rebuild/run the web site, the stored procedure assembly is rebuilt and re-deployed also (which is good) but because the stored procedures are recreated, the GRANT permissions previously on them are lost (which is not so good). I'd like to have the GRANT EXECUTE permissions added as an automatic part of the deployment process, if that is possible. Help?
Instead of rebuilding (dropping and creating) the procedures, could you use ALTER PROCEDURE syntax? If that's not an option, perhaps you could include a plain script to the deployment (depending how you do the deployment).
The need to optimize rises from a bad design.My articles[^]
-
Instead of rebuilding (dropping and creating) the procedures, could you use ALTER PROCEDURE syntax? If that's not an option, perhaps you could include a plain script to the deployment (depending how you do the deployment).
The need to optimize rises from a bad design.My articles[^]
Mika Wendelius wrote:
Instead of rebuilding (dropping and creating) the procedures, could you use ALTER PROCEDURE syntax? If that's not an option, perhaps you could include a plain script to the deployment (depending how you do the deployment).
If I were writing the deployment script, then sure, but I was hoping to stay within Visual Studio - it's just a lot more convenient to click the "run" button, have it go through the build and deployment, and run the web app. I was hoping that I've missed an option along the way that supports the execution of custom SQL scripts upon deployment... or some other means of addressing the stored procedure permissions. I haven't found anything like that in VS. You know, something like "Deployment Events..." similar to "Build Events..." would be exactly the thing.
-
Mika Wendelius wrote:
Instead of rebuilding (dropping and creating) the procedures, could you use ALTER PROCEDURE syntax? If that's not an option, perhaps you could include a plain script to the deployment (depending how you do the deployment).
If I were writing the deployment script, then sure, but I was hoping to stay within Visual Studio - it's just a lot more convenient to click the "run" button, have it go through the build and deployment, and run the web app. I was hoping that I've missed an option along the way that supports the execution of custom SQL scripts upon deployment... or some other means of addressing the stored procedure permissions. I haven't found anything like that in VS. You know, something like "Deployment Events..." similar to "Build Events..." would be exactly the thing.
Have you tried post build events? I'm not sure, but I think it's run as the last operation in building sequence. I did a little test and it seems that the post build command line (or actually I used a script) was run after the full build was done, but I think I may have a different situation since the target directory was actually the web folder.
The need to optimize rises from a bad design.My articles[^]
-
Have you tried post build events? I'm not sure, but I think it's run as the last operation in building sequence. I did a little test and it seems that the post build command line (or actually I used a script) was run after the full build was done, but I think I may have a different situation since the target directory was actually the web folder.
The need to optimize rises from a bad design.My articles[^]
Mika Wendelius wrote:
Have you tried post build events? I'm not sure, but I think it's run as the last operation in building sequence. I did a little test and it seems that the post build command line (or actually I used a script) was run after the full build was done, but I think I may have a different situation since the target directory was actually the web folder.
Hi Mika. Thanks for the replies. I guess that might work - a post-build event on the web project that issues the GRANT EXECUTE statements to the server... I can try that. It just seems like there should be a way to do this when compiling and deploying the stored procedures themselves, but the above might be a workaround. Thanks again.
-
Mika Wendelius wrote:
Have you tried post build events? I'm not sure, but I think it's run as the last operation in building sequence. I did a little test and it seems that the post build command line (or actually I used a script) was run after the full build was done, but I think I may have a different situation since the target directory was actually the web folder.
Hi Mika. Thanks for the replies. I guess that might work - a post-build event on the web project that issues the GRANT EXECUTE statements to the server... I can try that. It just seems like there should be a way to do this when compiling and deploying the stored procedures themselves, but the above might be a workaround. Thanks again.
Mike Ellison wrote:
the above might be a workaround.
If you have time, let us know if it worked. I think that there could be other workarounds also, but nothing comes in mind that would be any better than post event build. One thing I was thinking is that if the grant statements are fixed, could you actually do this in the database. What I mean is that if you would create a DDL trigger using CREATE_PROCEDURE event type, you could catch the procedure creation at the database and then execute the necessary grants...
Mike Ellison wrote:
Thanks again
You're welcome :)
The need to optimize rises from a bad design.My articles[^]
-
Mike Ellison wrote:
the above might be a workaround.
If you have time, let us know if it worked. I think that there could be other workarounds also, but nothing comes in mind that would be any better than post event build. One thing I was thinking is that if the grant statements are fixed, could you actually do this in the database. What I mean is that if you would create a DDL trigger using CREATE_PROCEDURE event type, you could catch the procedure creation at the database and then execute the necessary grants...
Mike Ellison wrote:
Thanks again
You're welcome :)
The need to optimize rises from a bad design.My articles[^]
Hi Mika. I ended up using a DDL trigger like the following:
CREATE TRIGGER GrantExecuteTrigger on DATABASE
FOR CREATE_PROCEDURE
AS
declare @user varchar(64)
set @user = 'whateverUserWeWant'declare @data xml
set @data = EVENTDATA()declare @sql varchar(max)
set @sql = 'GRANT EXECUTE ON '
+ @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)')
+ ' TO ' + @userexecute(@sql)
GO
This did work... every time the assembly/stored procs are deployed from Visual Studio, the trigger is applying the GRANT EXECUTE permission accordingly. I will just have to remember to adjust permissions on any stored procs that I would work with outside the VS context. Much of what I read regarding this issue indicated there was a
Script.PostDeployment.sqlfile that could be modified for this purpose, but I wonder if that is limited to the Database edition of Team Studio... we have the Architecture edition, and it doesn't seem like the same facility exists there (unless I'm completely missing it). -
Hi Mika. I ended up using a DDL trigger like the following:
CREATE TRIGGER GrantExecuteTrigger on DATABASE
FOR CREATE_PROCEDURE
AS
declare @user varchar(64)
set @user = 'whateverUserWeWant'declare @data xml
set @data = EVENTDATA()declare @sql varchar(max)
set @sql = 'GRANT EXECUTE ON '
+ @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)')
+ ' TO ' + @userexecute(@sql)
GO
This did work... every time the assembly/stored procs are deployed from Visual Studio, the trigger is applying the GRANT EXECUTE permission accordingly. I will just have to remember to adjust permissions on any stored procs that I would work with outside the VS context. Much of what I read regarding this issue indicated there was a
Script.PostDeployment.sqlfile that could be modified for this purpose, but I wonder if that is limited to the Database edition of Team Studio... we have the Architecture edition, and it doesn't seem like the same facility exists there (unless I'm completely missing it).Mike Ellison wrote:
I wonder if that is limited to the Database edition of Team Studio
Seems that i's only in Database Edition, http://msdn.microsoft.com/en-us/library/aa833410.aspx[^]
Mike Ellison wrote:
I will just have to remember to adjust permissions on any stored procs that I would work with outside the VS context.
If you want to put some time to this, one way could be that you: - create a table in your db where you will store the privileges that you want to grant to procedures (also grantees) - include a file containing the data for the grants to your VS project - using pre build events, first delete the grants from the table in the database - add new rows to the table based on the previous file (you could use sqlcmd or osql for this) Now when the procedure is created, your trigger could read the grant "profiles" from that table so you could control what privileges are granted using the file in VS project.
The need to optimize rises from a bad design.My articles[^]
-
Mike Ellison wrote:
I wonder if that is limited to the Database edition of Team Studio
Seems that i's only in Database Edition, http://msdn.microsoft.com/en-us/library/aa833410.aspx[^]
Mike Ellison wrote:
I will just have to remember to adjust permissions on any stored procs that I would work with outside the VS context.
If you want to put some time to this, one way could be that you: - create a table in your db where you will store the privileges that you want to grant to procedures (also grantees) - include a file containing the data for the grants to your VS project - using pre build events, first delete the grants from the table in the database - add new rows to the table based on the previous file (you could use sqlcmd or osql for this) Now when the procedure is created, your trigger could read the grant "profiles" from that table so you could control what privileges are granted using the file in VS project.
The need to optimize rises from a bad design.My articles[^]
Thanks for the suggestion, Mika, and I appreciate the conversation you and I have been having on this. It was very helpful.
-
Thanks for the suggestion, Mika, and I appreciate the conversation you and I have been having on this. It was very helpful.