InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 21:22:47 GMT20http://www.sqlservercentral.com/Forums/Topic835572-1365-1.aspxJonathan, though you wrote this 3 years ago, thank you VERY much for a clear, well-written, practical article that fixed a very thorny problem for me: allow users to execute a stored procedure to kick off a particular Agent job. You have no idea how much time and aggravation your article saved me. If only I'd looked here before wading through all my other Google results.... :ermm:RichFri, 24 Aug 2012 08:49:48 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic835572-1365-1.aspxExcellent article.I was having a permission denied on db_mail when sending a email from a sp that was being executed by Service Broker, and this was despite the user who was was executing existing in msdb.DatabaseMailUserRole and set trustworthy was on. Ran through the article, created the certificate and assigned it .... success. tyMurtaghMon, 16 Jul 2012 08:45:23 GMTBob hopwhttp://www.sqlservercentral.com/Forums/Topic835572-1365-1.aspx[quote][b]ssb-402814 (3/30/2011)[/b][hr]I have created a stored procedure in the msdb that will basically start a job and wait for completion. I have implemented the certificate signed stored procedure in one database successfully, but would now like to incorporate it into a second database. I was thinking I would be able to use the same certificate to make this happen (create in second database from file).This does not seem to be the case though. Doing a select on sys.certificates in the second database shows NA for pvt_key_encryption_type and NO_PRIVATE_KEY for pvt_key_encryption_type_desc. I receive the following error when trying to sign the stored procedure in the second database from this certificate Msg 15556, Level 16, State 1, Line 1Cannot decrypt or encrypt using the specified certificate, either because it has no private key or because the password provided for the private key is incorrect.I was wondering if this is possible. And if so if there are any reasons not to do this. I could see this expanding to other databases in the future and would like to use one certificate (since everything will be targeting the same stored procedure in the msdb database) instead of having to create numerous certificates, logins, users, etc.Any advise/information is much appreciated.Thanks,Sam[/quote]Sam,While you and I have passed emails to resolve the problem, I wanted to post an update here in the comments as well so that anyone else encountering this issue has the reference to how to resolve it. I posted a blog post that details the problem with a repro and then shows how to fix the issue by backing up the private key for the certificate when the certificate is backed up so that the private key can be created with the certificate from the backup files in the new database.[url=http://sqlskills.com/blogs/jonathan/post/Certificate-Signing-Stored-Procedures-in-Multiple-Databases.aspx]Certificate Signing Stored Procedures in Multiple Databases[/url]Tue, 26 Apr 2011 20:21:17 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic835572-1365-1.aspxI have created a stored procedure in the msdb that will basically start a job and wait for completion. I have implemented the certificate signed stored procedure in one database successfully, but would now like to incorporate it into a second database. I was thinking I would be able to use the same certificate to make this happen (create in second database from file).This does not seem to be the case though. Doing a select on sys.certificates in the second database shows NA for pvt_key_encryption_type and NO_PRIVATE_KEY for pvt_key_encryption_type_desc. I receive the following error when trying to sign the stored procedure in the second database from this certificate Msg 15556, Level 16, State 1, Line 1Cannot decrypt or encrypt using the specified certificate, either because it has no private key or because the password provided for the private key is incorrect.I was wondering if this is possible. And if so if there are any reasons not to do this. I could see this expanding to other databases in the future and would like to use one certificate (since everything will be targeting the same stored procedure in the msdb database) instead of having to create numerous certificates, logins, users, etc.Any advise/information is much appreciated.Thanks,SamWed, 30 Mar 2011 07:12:19 GMTssb-402814http://www.sqlservercentral.com/Forums/Topic835572-1365-1.aspxAnother question, I'm stupified by the following:I wish to allow non-sa users to run a bcp string with xp_cmdshell. The option to use this XP is by default turned off. I can get these non-sa users to run the xp_cmdshell, it's the turning on and off of the configuration which I cannot resolve.What I'm looking at is a SP like this:[code="sql"]CREATE PROCEDURE testSP1BEGIN EXEC SP_CONFIGURE 'xp_cmdshell', 1; RECONFIGURE WITH OVERRIDE; EXEC XP_CMDSHELL 'DIR c:\' -- some VBCP command, but for a simple example... EXEC SP_CONFIGURE 'xp_cmdshell', 0; RECONFIGURE WITH OVERRIDE;ENDGO[/code]I have tried constructions with EXECUTE AS and Certificates, but sofar nada. Scoured the Internet, no example specifically on permissions on SP_CONFIGURE.Any suggestions?Wed, 02 Jun 2010 05:34:39 GMTFreeHansjehttp://www.sqlservercentral.com/Forums/Topic835572-1365-1.aspxBrilliant article.Only one thing which is a bit of a shame - this only works if the SP is execute as owner, not if it's execute as caller.As a result, if you look in msdb.dbo.sysmail_allitems, all items sent with this method say they were sent by sa. Also it's allowed to send using private profiles to which nobody has been granted access.If you send the mail by adding the user to the msdb role instead, then msdb.dbo.sysmail_allitems details the correct sender.DatabaseMail is great, but the permissions are a real struggle. In addition to the sp_send_dbmail permission problem, it's hard to get the permissions sorted out for private profiles. All the documentation says that you can grant permissions on private profiles to database roles in msdb, but in actual fact it won't let you do this.Rachel.Thu, 25 Mar 2010 09:08:41 GMTRachel Byfordhttp://www.sqlservercentral.com/Forums/Topic835572-1365-1.aspx[quote][b]Jonathan Kehayias (1/22/2010)[/b][hr][quote][b]ChiragNS (1/18/2010)[/b][hr]Great article and here comes another dumb question!After signing the procedure with certificate can the procedure be executed only by the login/user created from the certificate? Can other users execute the procedure.[/quote]No, you can GRANT EXECUTE on the stored procedure that is signed by the certificate to any database user. That's the point behind using the certificate signed stored procedure to grant access. The users don't have to have elevated rights, they only need the ability to EXECUTE the signed stored procedure, or a parent procedure in the ownership chain.[/quote]Thank you.Mon, 25 Jan 2010 05:07:21 GMTChiragNShttp://www.sqlservercentral.com/Forums/Topic835572-1365-1.aspx[quote][b]FreeHansje (1/18/2010)[/b][hr]Jonathan,I wish to say thanks again. I must have done something wrong, different then you describe in your solution. I could have sworn I used the same construction to test permission on the DMV's, but could not get it working. But as I said, I must have donw something different, and since there is no1 at my current position with the knowledge to talk this over I could not pinpoint the error.Also tnx for showing that my initial 'solution' did not work. With your suggestion I can get things working.Tnx again, I have learned a lot today.[/quote]Did you figure your problem out? If not, DM me or shoot me a contact through my blog on SQLBlog and I'll see if I can help you troubleshoot the issue further.Fri, 22 Jan 2010 22:00:55 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic835572-1365-1.aspx[quote][b]ChiragNS (1/18/2010)[/b][hr]Great article and here comes another dumb question!After signing the procedure with certificate can the procedure be executed only by the login/user created from the certificate? Can other users execute the procedure.[/quote]No, you can GRANT EXECUTE on the stored procedure that is signed by the certificate to any database user. That's the point behind using the certificate signed stored procedure to grant access. The users don't have to have elevated rights, they only need the ability to EXECUTE the signed stored procedure, or a parent procedure in the ownership chain.Fri, 22 Jan 2010 21:58:48 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic835572-1365-1.aspxHi anyone there!!Fri, 22 Jan 2010 05:33:16 GMTChiragNShttp://www.sqlservercentral.com/Forums/Topic835572-1365-1.aspxGreat article and here comes another dumb question!After signing the procedure with certificate can the procedure be executed only by the login/user created from the certificate? Can other users execute the procedure.Mon, 18 Jan 2010 05:33:17 GMTChiragNShttp://www.sqlservercentral.com/Forums/Topic835572-1365-1.aspxJonathan,I wish to say thanks again. I must have done something wrong, different then you describe in your solution. I could have sworn I used the same construction to test permission on the DMV's, but could not get it working. But as I said, I must have donw something different, and since there is no1 at my current position with the knowledge to talk this over I could not pinpoint the error.Also tnx for showing that my initial 'solution' did not work. With your suggestion I can get things working.Tnx again, I have learned a lot today.Mon, 18 Jan 2010 05:16:25 GMTFreeHansjehttp://www.sqlservercentral.com/Forums/Topic835572-1365-1.aspxTnx Jonathan, this is really hel;pful, in several ways.Thu, 14 Jan 2010 00:10:32 GMTFreeHansjehttp://www.sqlservercentral.com/Forums/Topic835572-1365-1.aspxFor those interested, you can use the same method to give SA rights or access like VIEW SERVER STATE to users with no access. For example to call sys.dm_exec_sql_text() as originally requested:[code="sql"]USE [master]GOCREATE DATABASE SAPermsTestGOCREATE LOGIN [PermsTestLogin] WITH PASSWORD=N'c0mpl3xp@$$'GOUSE [SAPermsTest]GO CREATE USER [PermsTestLogin] FROM LOGIN [PermsTestLogin]GOCREATE TABLE PermsTest(RowID int identity primary key)GOGRANT INSERT ON PermsTest TO PermsTestLoginGOCREATE TRIGGER Audit_PermsTestON PermsTestWITH EXECUTE AS OWNERFOR INSERT, UPDATE, DELETEASBEGIN DECLARE @TEMP TABLE (EventType nvarchar(30), Parameters int, EventInfo nvarchar(4000)) INSERT INTO @TEMP EXEC('DBCC INPUTBUFFER(@@SPID)') SELECT EventInfo FROM @TEMP SELECT SYSTEM_USER SELECT text FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) WHERE session_id = @@SPIDENDGO-- Test the trigger will fail. Make sure to revertEXECUTE AS LOGIN='PermsTestLogin'INSERT INTO PermsTest default values;REVERTGO-- Revert Fails when trigger execution fails so do it now.REVERTGO-- Create a certificate to sign stored procedures withCREATE CERTIFICATE [SAPermsCertificate]ENCRYPTION BY PASSWORD = '$tr0ngp@$$w0rd'WITH SUBJECT = 'Certificate for signing Audit Triggers';GO-- Backup certificate so it can be create in master databaseBACKUP CERTIFICATE [SAPermsCertificate]TO FILE = 'D:\SQLBackups\SAPermsCertificate.CER';GO-- Add Certificate to Master DatabaseUSE [master]GOCREATE CERTIFICATE [SAPermsCertificate]FROM FILE = 'D:\SQLBackups\SAPermsCertificate.CER';GO-- Create a login from the certificateCREATE LOGIN [SAPermsLogin]FROM CERTIFICATE [SAPermsCertificate];GO-- The Login must have Authenticate Sever to access server scoped system tables-- per http://msdn.microsoft.com/en-us/library/ms190785.aspxGRANT AUTHENTICATE SERVER TO [SAPermsLogin]GO-- Add the VIEW Server State permission to the Certificate login.GRANT VIEW SERVER STATE TO [SAPermsLogin]GOUSE [SAPermsTest]GO-- Sign the procedure with the certificate's private keyADD SIGNATURE TO OBJECT::Audit_PermsTestBY CERTIFICATE [SAPermsCertificate] WITH PASSWORD = '$tr0ngp@$$w0rd';GO-- Retest the Trigger function and it will work returning -- CREATE TRIGGER statement for the sys.dm_exec_sql_text()EXECUTE AS LOGIN='PermsTestLogin'INSERT INTO PermsTest default values;REVERTGO-- Cleanup/*USE [master]GODROP LOGIN [SAPermsLogin]DROP CERTIFICATE [SAPermsCertificate]DROP DATABASE [SAPermsTest]-- Delete the certificate backup from disk*/[/code]If you run the above, you will note that as I stated in my last response the sys.dm_exec_sql_text() DMF will not output the last statement called by the client it will output the last statement executed by the session which happens to be the execution of the trigger itself. Kind of self defeating code, at least in my opinion which is why I still fall back on good ole' trusty DBCC INPUTBUFFER to get the last input statement from the client.Mon, 11 Jan 2010 12:13:19 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic835572-1365-1.aspx[quote][b]FreeHansje (1/11/2010)[/b][hr]I thought I had found the solution for my problem, but alas...I need to call master.sys.dm_exec_sql_text(to find the last used sql-statement) from a trigger, or sp, in another database, by ordinary users without SA-rights; please don't ask why. I changed reference in your article from msdb into master, used GRANT VIEW SERVER STATE as authorization, but alas, no succes. For days now I am looking for a way to allow non-SA users to view DMV's. Any1 here can help me out?![/quote]You actually don't need any kind of special permissions to solve your problem. First sys.dm_exec_sql_text isn't going to give you the information that you probably want. If you run that for a session_id in a trigger, it is going to return the CREATE TRIGGER statement of the trigger firing the request. As long as the need to get the SQL Statement that was executed to cause the trigger to fire is self session scoped, you can get it without added permissions using DBCC INPUTBUFFER() like follows:[code="sql"]CREATE TABLE PermsTest(RowID int identity primary key)GOCREATE TRIGGER Audit_PermsTestON PermsTestWITH EXECUTE AS OWNERFOR INSERT, UPDATE, DELETEASBEGIN DECLARE @TEMP TABLE (EventType nvarchar(30), Parameters int, EventInfo nvarchar(4000)) INSERT INTO @TEMP EXEC('DBCC INPUTBUFFER(@@SPID)') SELECT EventInfo FROM @TEMPENDGO[/code]If you want to get information from another session that is executing that would require the additional permissions. As long as it stays self scoped it can be called without GRANT VIEW SERVER STATE.Mon, 11 Jan 2010 11:56:42 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic835572-1365-1.aspxI thought I had found the solution for my problem, but alas...I need to call master.sys.dm_exec_sql_text(to find the last used sql-statement) from a trigger, or sp, in another database, by ordinary users without SA-rights; please don't ask why. I changed reference in your article from msdb into master, used GRANT VIEW SERVER STATE as authorization, but alas, no succes. For days now I am looking for a way to allow non-SA users to view DMV's. Any1 here can help me out?!Mon, 11 Jan 2010 05:51:09 GMTFreeHansjehttp://www.sqlservercentral.com/Forums/Topic835572-1365-1.aspxThanks for the great read.Sat, 09 Jan 2010 22:00:59 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic835572-1365-1.aspx[quote][b]Uma-419424 (1/8/2010)[/b][hr]Simply give execute permission on the sp "sp_send_dbmail" to the role "public" in the database "msdb".[/quote]That would violate Audit rules at my company, and most publicly held companies as well. In fact we have a script that locks down the public role further than it is at installation. If you develop software for sale, you have to keep things like Audit's in mind or you can severely limit your customer base.Fri, 08 Jan 2010 17:37:10 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic835572-1365-1.aspxGreat article I found after searching for long time in Internet about impersonation and cross database accessing.I got one simple solution perticularly for database mail.Simply give execute permission on the sp "sp_send_dbmail" to the role "public" in the database "msdb".Any call to this sp from other database/sp/function/.. with impersination (or without impersination I hope) will be take care.You can get rid of the most annoying error "The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'"Fri, 08 Jan 2010 15:57:14 GMTSQLEnthuhttp://www.sqlservercentral.com/Forums/Topic835572-1365-1.aspx[quote][b]simon.murin (12/17/2009)[/b][hr]CREATE PROCEDURE [dbo].[TestSendMail].....WITH EXECUTE AS &lt;account with rights to send e-mails&gt;Wouldn't it be easier that way?[/quote]Have you tried it without TRUSTWORTHY ON? It won't work for a number of reasons. First the possible values for the EXECUTE AS clause are [quote][b][url=http://msdn.microsoft.com/en-us/library/ms188354.aspx]EXECUTE AS Clause (Transact-SQL)[/url][/b][hr]Functions (except inline table-valued functions), Stored Procedures, and DML Triggers{ EXEC | EXECUTE } AS { CALLER | SELF | OWNER | 'user_name' } [/quote]You would have to specify a login_name, not a user_name because this stored procedure is in a User Database and the permissions needed to execute sp_send_dbmail are needed in msdb. The database user in a user database has no rights or permissions in another database, the login associated with that user does. TRUSTWORTHY ON would be required to make that work, which has additional ramifications security wise.[quote][b]From the Article....[/b][hr]This allows for Impersonation across databases to gain access to resources in other databases on the server. It is not a secure method of solving the problem since having TRUSTWORTHY ON allows for other side effects. If the database owner is a sysadmin, then TRUSTWORTHY ON provides full access to the server. This flag is also set OFF whenever a database is attached or restored to SQL Server. This is an easy to miss item that can become problematic in the worst possible times, like during disaster recovery.[/quote]Thu, 17 Dec 2009 16:21:46 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic835572-1365-1.aspxCREATE PROCEDURE [dbo].[TestSendMail].....WITH EXECUTE AS &lt;account with rights to send e-mails&gt;Wouldn't it be easier that way?Thu, 17 Dec 2009 16:10:18 GMTsimon.murinhttp://www.sqlservercentral.com/Forums/Topic835572-1365-1.aspxIt's not a stupid question and it is easy to miss even if you are reading through it. I just wasn't sure how to answer the question being asked based on the information provided in the question.Thu, 17 Dec 2009 13:37:56 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic835572-1365-1.aspx[quote][b]Jonathan Kehayias (12/17/2009)[/b][hr]The stored procedure in the article uses WITH EXECUTE AS. Not sure what you are asking specifically or that you read the article entirely or tested the code in it.[/quote]I knew it was a dumb question!Yeah... I stopped reading right before you got to that point, because I thought you were skipping over a solution I'd used in the past. Wow, your solution is far superior.Thu, 17 Dec 2009 12:01:46 GMTjohnzabroskihttp://www.sqlservercentral.com/Forums/Topic835572-1365-1.aspx[quote][b]johnzabroski (12/17/2009)[/b][hr]Dumb question...What's wrong with using WITH EXECUTE AS? http://www.mssqltips.com/tip.asp?tip=1227[/quote]The stored procedure in the article uses WITH EXECUTE AS. Not sure what you are asking specifically or that you read the article entirely or tested the code in it.Thu, 17 Dec 2009 11:37:58 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic835572-1365-1.aspxDumb question...What's wrong with using WITH EXECUTE AS? http://www.mssqltips.com/tip.asp?tip=1227Thu, 17 Dec 2009 11:25:19 GMTjohnzabroskihttp://www.sqlservercentral.com/Forums/Topic835572-1365-1.aspxYou can see how to use a certificate signed assembly on my article [url=http://www.sqlservercentral.com/articles/SQLCLR/65656/] Trading in xp_cmdshell for SQLCLR (Part 1) - List Directory Contents[/url] to keep from having to set TRUSTWORTHY ON.Thu, 17 Dec 2009 10:53:32 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic835572-1365-1.aspxHi,Really interesting for me also.Could that be applied in the case of a CLR stored proc and what dbrole could be used for certified login to allow calling webservices through CLR? Currently, I use Trustworthy on for my .net proc to succeed but I'd prefer not to have to do that.Thanks in advanceThu, 17 Dec 2009 10:19:12 GMTdan77euhttp://www.sqlservercentral.com/Forums/Topic835572-1365-1.aspxGreat article Jonathan. Makes everything clear. Signing a module is very useful in many areas.Thu, 17 Dec 2009 08:42:51 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic835572-1365-1.aspxgreat articleno need for it, but into the briefcase it goes in case i'll need it in the futureThu, 17 Dec 2009 06:54:21 GMTalen teplitskyhttp://www.sqlservercentral.com/Forums/Topic835572-1365-1.aspxThat is terrific Jonathan, and to my mind, a great solution generally for elevating the rights of a stored procedure (especially a stored proc that has to use xp_cmdshell or oa_create methods).An easy to follow article 10/10 !Wed, 16 Dec 2009 23:48:03 GMTC# Gnuhttp://www.sqlservercentral.com/Forums/Topic835572-1365-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Security/68873/">Using a Certificate Signed Stored Procedure to Execute sp_send_dbmail</A>[/B]Wed, 16 Dec 2009 23:43:22 GMTJonathan Kehayias