|
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).
|
|
|
|
|
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.
|
|
|
|
|
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 
|
|
|
|
|
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 ' + @user
execute(@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.sql file 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.
|
|
|
|
|
Thanks for the suggestion, Mika, and I appreciate the conversation you and I have been having on this. It was very helpful.
|
|
|
|
|
Mike Ellison wrote: It was very helpful.
Glad to hear that You're welcome.
|
|
|
|
|
Hi,
I'm trying to use the query below in MSSQL.
<br />
SELECT [mxDelete],[mxContactID] <br />
FROM mxContact.dbo.Contacts <br />
WHERE mxContactID IN <br />
<br />
(SELECT [mxContactID], [mxzCopies]<br />
FROM <br />
(SELECT [mxContactID], [mxzCopies]<br />
FROM mxContact.dbo.Associations_Groups_to_Contacts <br />
WHERE mxGroupID='35EA110C-FE44-4A85-8D9A-AE973E2C5F85' <br />
OR mxGroupID='3CF66F58-F8E4-44CD-84A0-CB62F85E129B') <br />
AS COL<br />
GROUP BY mxContactID HAVING (COUNT(mxContactID) > 1))<br />
My question is, how can I make it so that the code in bold will give me the mxzCopies column as well.
If I select just the contactID, then it works fine, but with the addition of mxzCopies, it gives me:
Msg 8120, Level 16, State 1, Line 1
Column 'COL.mxzCopies' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Cheers,
Mark.
Mark Brock
"We're definitely not going to make a G or a PG version of this. It's not PillowfightCraft." -- Chris Metzen
Click here to view my blog
|
|
|
|
|
Nevermind figured it out.
<br />
SELECT mxContactID, mxzCopies<br />
FROM <br />
(<br />
SELECT [mxContactID], [mxzCopies]<br />
FROM mxContact.dbo.Associations_Groups_to_Contacts <br />
WHERE mxGroupID='35EA110C-FE44-4A85-8D9A-AE973E2C5F85' <br />
OR mxGroupID='3CF66F58-F8E4-44CD-84A0-CB62F85E129B'<br />
)<br />
AS COL<br />
GROUP BY mxContactID, mxzCopies HAVING (COUNT(mxContactID) > 1)<br />
Mark Brock
"We're definitely not going to make a G or a PG version of this. It's not PillowfightCraft." -- Chris Metzen
Click here to view my blog
|
|
|
|
|
whenever i was trying to do this .. exception occurred.
Please help me out.
Thanks in Advance.
|
|
|
|
|
In short, no.
However, you can export the database in 2005 (scripts, csv etc) and import the data in 2000 as long as you don't use 2005 specific features or datatypes.
|
|
|
|
|
XQuery - its a tricky beast.
Have a look at the script below, I'm creating a simple xml document in a single row in a temporary table. What I want to do is update EVERY value element and set it to D. There's three, A, B and C, and I want all three to be updated to D.
The script complains that I'm attempting to update more than one node. Does this mean I have to use some iterative approach to do this? Any ideas anyone?
CREATE TABLE #temp ( myXml xml )
INSERT INTO #temp VALUES ('<document><value>A</value><value>B</value><value>C</value></document>')
select * from #temp
update #temp set myXml.modify('replace value of (//value/text()) with "D"')
drop table #temp
Regards,
Rob Philpott.
|
|
|
|
|
Rob Philpott wrote: Does this mean I have to use some iterative approach to do this?
Yes, you'll have to iterate through the nodes. SQL Server doesn't support multiple node updates at once.
One way you could do this is something like this:
declare @counter int
set @counter = 1
while (@counter <= 3)
begin
update #temp
set myXml.modify('replace value of (/document/value[sql:variable("@counter")]/text())[1] with "somevalue"')
set @counter = @counter + 1
end
|
|
|
|
|
Perfect. Thanks Mika.
Regards,
Rob Philpott.
|
|
|
|
|
|
Hi,
I am considering using Mono to develop an specific purpose image browser to run on Windows, MAC/OS and Linux.
To meet performance requirements the application requires a database to store thumbnails and search data.
I am looking for suggestions for database products that run on Windows, MAC/OS and Linux that do not require use of technologies such as VMWare or Parallels, which I could use from within a Mono/C# environment.
The application along with the database and the images will be distributed at no cost to "qualified" users, so I'm looking for a zero cost run time database system. The application must run stand-alone - i.e. no network, no servers,
The thumbnails and search data would be imported from the original image files.
The search data would be sourced from embedded IPTC metadata such as keywords and supplemental categories.
The thumbnail and IPTC metadata are injected into the image files using a commercial Digital Asset Management (DAM) application.
I don't insist that the "database" be SQL compliant, b-tree indexes would probably suffice. As long as the same database or index files can be used from within the same Mono application running on Windows, MAC/OS or Linux.
TIA
|
|
|
|
|
|
Hi Mika,
An earlier experience with MySQL was not a happy one so I'd more or less dismissed it, also like many of the others its overkill for my needs.
However SQLite looks good, I like the idea of a using library rather than a separate process. Wonder if anyone's used in a Mono/C# app. I found this[^] nearby, it should get me off the ground.
Thanks heaps - PhilD 
|
|
|
|
|
|
Mika Wendelius wrote: If you don't need a "byrocratic" installation, SQLite is most propably a good choice
Nuh, I prefer "graphitic" installs, they're much easier to rub out.
Thanks for doing my research, I put myself on the ximian mailing lists earlier this evening.
rgds PhilD
|
|
|
|
|
Hello everybody,
i have 3 tables Student, Instructor, and Allocation. I have 5 students and 2 instructors how can I allocate 2 instructors to 5 students for project supervision.
StudentId StudentName
1 Learner
2 Paul
3 Griffin
4 Inese
5 Kelly
InstructorId InstructorName
1 Sharma
2 Bill
this is out put table
AllocationId InstructorName StudentName
001 Sharma Learner
002 Sharma Paul
003 Bill Griffin
004 Bill Inese
005
where the Kelly will go?
please any body could give me a clue how should I do it either with queries or mathematical calculations.
let suppose if I write a stored procedure 'Allocation' which take two inputs students and instructors like Allocation 5,2 how it will work.
thnx in adv for any kind help.
regards
learner
|
|
|
|
|
usually, when creating this sort of association you use the ID fields from Student and Instructor table.
But you've answered your own question - If Kelly's instructor is Bill then:
001 Sharma Learner
002 Sharma Paul
003 Bill Griffin
004 Bill Inese
005 Bill Kelly
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
