InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 22:45:36 GMT20http://www.sqlservercentral.com/Forums/Topic1431780-392-1.aspxHere it is, with a bit more explanations:[url=http://blog.sqlxdetails.com/procedure-with-execute-as-login/]http://blog.sqlxdetails.com/procedure-with-execute-as-login/[/url]Sat, 23 Mar 2013 20:12:46 GMTVedran Kesegichttp://www.sqlservercentral.com/Forums/Topic1431780-392-1.aspx[quote][b]datwungai (3/22/2013)[/b][hr]Thanks Vedran!The certificate works like a charm!dwg[/quote]I'm glad to help. I'll put the script and explanations on my blog.Sat, 23 Mar 2013 03:35:37 GMTVedran Kesegichttp://www.sqlservercentral.com/Forums/Topic1431780-392-1.aspxThanks Eugene!Fri, 22 Mar 2013 11:08:46 GMTdatwungaihttp://www.sqlservercentral.com/Forums/Topic1431780-392-1.aspxWell said Jason.I've had that meeting with our respective boss' - the underpinning is a political game that I can't win necessarily. However, I've played this game before and religiously document and audit where ever I can. With that, I've caught him off-guard and had him stammering in a room full of people who were ready to believe he needed the access, and he ultimately didn't get it. Small victory for me.My whole MO is to keep it professional and act in good faith with regard to my duties as a DBA and it serves me well. I would have much rather partnered up with this guy because he's a very smart person and would've been a great asset, but his attitude killed it all when he felt he didn't need to treat me with respect because I didn't give him what he wants. And since he was a former DBA at this company, you'd think he'd be more understanding when it comes to the constraints IS has to work under (CM policies, etc.), but he chose the "Uncle Tom" route and burned just about every bridge he had in IS - now nobody wants to help this guy with anything.What can you do when grown men behave like children? My philosiphy: "It's not my kid."Fri, 22 Mar 2013 11:08:27 GMTdatwungaihttp://www.sqlservercentral.com/Forums/Topic1431780-392-1.aspxThanks Stuart, my sentiments exactly. It's not the presidency, if you forfiet your title here, it's gone for good!Fri, 22 Mar 2013 10:56:55 GMTdatwungaihttp://www.sqlservercentral.com/Forums/Topic1431780-392-1.aspxThanks Vedran!The certificate works like a charm!dwgFri, 22 Mar 2013 10:54:33 GMTdatwungaihttp://www.sqlservercentral.com/Forums/Topic1431780-392-1.aspxEugene, very well put.I personally would arrange a meeting with you, the developer involved, his boss and your boss, and ask him to state why he needs these rights. Then put a case where you can offer him something like the View Database state on Dev boxes only and maybe UAT, but production is off limits. If he keeps pushing the case for SA rights simply state that he would also then need to take part responsibility for those boxes where hes grated those rights such as working weekends as part of being on call, doing the mundane daily work.I would also give him a specific login for this rather than grant his standard network account with SA rights.You might then see him back down quite quickly. I'd also set up server level auditing so that if/when he screws up you have him by the soft and dangly things, and you have the perfect ammunition to get his rights revoked or have him removed, and you get the got the added bonus of the fact he did it all by himself. ;-)Wed, 20 Mar 2013 04:34:25 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1431780-392-1.aspx[quote][b]datwungai (3/19/2013)[/b][hr][quote]What exactly are you afraid of? [/quote]It's a former DBA that hates me because unlike him, I take my job seriously and won't give him sa permissions. He thinks because he "used to be a DBA at this company" that he's still entitled to the luxuries of sa rights without the responsibility of keeping the servers up and operational. Clear as mud?[/quote]I do like your work ethic! Just as mater of interest, why just simply not kill him? There are different options available: poison, knife, gun, box of dynamite at the end. Exterminate! :w00t:On a serious note, if your users just an ordinary database/application users, your are absolutely right. Granting this permission is too generous for them. Saying that, they would hardly ever ask for this one anyway. However if your user is a developer and that is development or test environment , then this permission is very important as it allows to monitor server activity and performance. I can hardly see how access to server stats effects "keeping the servers up and operational". But again, if he rally hates you, just offer him cup of coffee or tea, then just add a bit of polonium 210 and your enemy will be destroyed! :hehe:Wed, 20 Mar 2013 03:35:21 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1431780-392-1.aspx[quote][b]datwungai (3/19/2013)[/b][hr][quote]What exactly are you afraid of? [/quote]It's a former DBA that hates me because unlike him, I take my job seriously and won't give him sa permissions. He thinks because he "used to be a DBA at this company" that he's still entitled to the luxuries of sa rights without the responsibility of keeping the servers up and operational. Clear as mud?[/quote]Then you are taking the right stance if they are a [b]former[/b] DBA - they now have no right to access (except though any application)- stick to your guns.Wed, 20 Mar 2013 02:21:29 GMTStuart Davieshttp://www.sqlservercentral.com/Forums/Topic1431780-392-1.aspxHere is a complete and tested example with signed procedure:[code="sql"]-- Script by Vedran Kesegic, 20.3.2013.-- We will create a database and a procedure which calls sp_who2.-- We will also create a low-privileged login and give him exec permission on that procedure.create database TestCertcreate login LowPriv with password='Str0ngPWD!'GOuse TestCertcreate user LowPriv for login LowPrivGOcreate procedure dbo.show_processes --with execute as 'dbo' -- even this wont help, because dbo is db user and can receive only privileges at db level, not server levelasbegin select * from sys.login_token -- who am i select * from sys.fn_my_permissions(null,'server') -- what server rights do i have exec sys.sp_who2endGOgrant exec on dbo.show_processes to LowPrivGOexecute as login='LowPriv'exec sys.sp_who2 -- shows only my session, not goodexec dbo.show_processes -- shows only my session, not goodrevertGO-- Create login to which we will grant view server state. Login will be mapped to certificate.-- Sign procedure with that certificate - meaning procedure will execute under that high-priv login.-- certificate must be in master database in order to be mapped to loginuse master -- create certificate in master databasecreate certificate HighPrivCert ENCRYPTION BY PASSWORD = 'Str0ngPWD!'WITH SUBJECT = 'Certificate for signing stored procedures'select * from sys.certificatesGOcreate login HighPrivCertLogin from certificate HighPrivCert -- create high priv logingrant view server state to HighPrivCertLoginGO-- in order to grant db user rights, we must transfer to that db the same certificatebackup certificate HighPrivCert to file='C:\temp\HighPrivCert.CER'WITH PRIVATE KEY( FILE = 'C:\temp\HighPrivCert.PVK', DECRYPTION BY PASSWORD = 'Str0ngPWD!', -- pwd to open the key ENCRYPTION BY PASSWORD = 'FilePWD!1' -- must protect the file)GOuse TestCertcreate certificate HighPrivCert from file='C:\temp\HighPrivCert.CER'WITH PRIVATE KEY( FILE = 'C:\temp\HighPrivCert.PVK', DECRYPTION BY PASSWORD = 'FilePWD!1', -- to read the file ENCRYPTION BY PASSWORD = 'Str0ngPWD!' -- pwd to protect the key)select * from sys.certificates-- Manually delete cert files NOW!GO-- by signing procedure, it will run under login associated with that certificateADD SIGNATURE TO OBJECT::dbo.show_processesBY CERTIFICATE HighPrivCertWITH PASSWORD='Str0ngPWD!'GOexecute as login='LowPriv'exec sys.sp_who2 -- shows only my sessionexec dbo.show_processes -- shows all sessions! We are running under HighPrivCertLogin which have VIEW SERVER STATE permission.revertGO-- CLEANUP/*use masterdrop database TestCertdrop login HighPrivCertLogindrop certificate HighPrivCertdrop login LowPriv*/[/code]Enjoy!Tue, 19 Mar 2013 18:30:30 GMTVedran Kesegichttp://www.sqlservercentral.com/Forums/Topic1431780-392-1.aspx[quote]What exactly are you afraid of? [/quote]It's a former DBA that hates me because unlike him, I take my job seriously and won't give him sa permissions. He thinks because he "used to be a DBA at this company" that he's still entitled to the luxuries of sa rights without the responsibility of keeping the servers up and operational. Clear as mud?Tue, 19 Mar 2013 14:42:41 GMTdatwungaihttp://www.sqlservercentral.com/Forums/Topic1431780-392-1.aspxThank you everyone!SQLX - both great ideas, I'm going to try the first one... um... first.DWGTue, 19 Mar 2013 14:39:49 GMTdatwungaihttp://www.sqlservercentral.com/Forums/Topic1431780-392-1.aspxAlternative would be to grant "VIEW DATABASE STATE" to the user (or better, db role) in each database you want that login to view the sessions.After that, you do not need a special procedure, they can call sp_who2 directly and see only sessions on databases you have granted to.That is less secure than signing method because VIEW DATABASE STATE permission also grants access to dmv's (limited to that databases), which might be not what you want.Mon, 18 Mar 2013 10:25:37 GMTVedran Kesegichttp://www.sqlservercentral.com/Forums/Topic1431780-392-1.aspxWith "with execute as ..." clause on a procedure/function/trigger you define a database user to execute under, not a server login.Database user cannot have a server-level privilege such as viewing server processes. Only login can, with granted server-level permissions.That means you cannot use "with execute as" to view all the sessions on the server or any other server-scope info.Solution is this:You will build a plain procedure, with no "execute as" clause added to the procedure, and make it run under a highly priviledged LOGIN by signing the procedure with the certificate. Then grant execute on that procedure to a low-priviledged user (or better, not directly to a user but grant it to a database role and assign the role to the user).It's not that hard as it sounds. That is the only secure way you can grant specific sysadmin-only activities to a low priviledged login.Here is one example: [url=http://colleenmorrow.com/2011/12/19/tossing-pebbles-using-certificates-to-grant-privileges/]http://colleenmorrow.com/2011/12/19/tossing-pebbles-using-certificates-to-grant-privileges/[/url]Mon, 18 Mar 2013 10:11:02 GMTVedran Kesegichttp://www.sqlservercentral.com/Forums/Topic1431780-392-1.aspx[quote]... but I don't want to grant view server state permissions and all that it exposes...[/quote]What exactly are you afraid of? What dangerous things does it expose other than ability to use DMV's and DMF's to monitor server health/problems and tune performance?Mon, 18 Mar 2013 03:13:50 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1431780-392-1.aspxIf you only want the results for the user have you tried something along the lines of this:-[code="other"]USE [master]GO/****** Object: StoredProcedure [dbo].[usp_who] Script Date: 03/14/2013 14:57:25 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================-- Author: -- Created: 03/14/13-- Desc: sp_who for users-- =============================CREATE PROCEDURE [usp_who] @who INT = @@spidwith execute as selfASBEGINSET NOCOUNT ON;exec sp_who @who;END[/code]Mon, 18 Mar 2013 02:28:50 GMTStuart Davieshttp://www.sqlservercentral.com/Forums/Topic1431780-392-1.aspxYou can try creating temporary table in your procedurewhere all the information for sp_who will be stored so that we can get the information from the table as you likestatement areinsert #temptableexec sp_whoMon, 18 Mar 2013 00:38:50 GMTjeetsingh.cshttp://www.sqlservercentral.com/Forums/Topic1431780-392-1.aspxHello All,Couldn't find anything that addresses this specifically, so here goes.I have a group that I want to allow to view process on their dev server, but I don't want to grant view server state permissions and all that it exposes, so I'm trying to write a proc that will execute sp_who as me (the DBA) for users, but it still only returns one row instaed of all of them; what am I missing? MS's docs say this should work.USE [master]GO/****** Object: StoredProcedure [dbo].[usp_who] Script Date: 03/14/2013 14:57:25 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================-- Author: -- Created: 03/14/13-- Desc: sp_who for users-- =============================ALTER PROCEDURE [usp_who]with execute as selfASBEGIN SET NOCOUNT ON; exec sp_who;ENDFri, 15 Mar 2013 15:09:51 GMTdatwungai