Custom sp_who

  • Hello 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 ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    -- =============================

    -- Author:

    -- Created: 03/14/13

    -- Desc:sp_who for users

    -- =============================

    ALTER PROCEDURE [usp_who]

    with execute as self

    AS

    BEGIN

    SET NOCOUNT ON;

    exec sp_who;

    END

  • You can try creating temporary table in your procedure

    where all the information for sp_who will be stored so that we can

    get the information from the table as you like

    statement are

    insert #temptable

    exec sp_who

  • If you only want the results for the user have you tried something along the lines of this:-

    USE [master]

    GO

    /****** Object: StoredProcedure [dbo].[usp_who] Script Date: 03/14/2013 14:57:25 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    -- =============================

    -- Author:

    -- Created: 03/14/13

    -- Desc: sp_who for users

    -- =============================

    CREATE PROCEDURE [usp_who] @who INT = @@spid

    with execute as self

    AS

    BEGIN

    SET NOCOUNT ON;

    exec sp_who @who;

    END

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • ... but I don't want to grant view server state permissions and all that it exposes...

    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?

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • With "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: http://colleenmorrow.com/2011/12/19/tossing-pebbles-using-certificates-to-grant-privileges/[/url]

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths
  • Alternative 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.

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths
  • Thank you everyone!

    SQLX - both great ideas, I'm going to try the first one... um... first.

    DWG

  • What exactly are you afraid of?

    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?

  • Here is a complete and tested example with signed procedure:

    -- 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 TestCert

    create login LowPriv with password='Str0ngPWD!'

    GO

    use TestCert

    create user LowPriv for login LowPriv

    GO

    create 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 level

    as

    begin

    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_who2

    end

    GO

    grant exec on dbo.show_processes to LowPriv

    GO

    execute as login='LowPriv'

    exec sys.sp_who2 -- shows only my session, not good

    exec dbo.show_processes -- shows only my session, not good

    revert

    GO

    -- 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 login

    use master -- create certificate in master database

    create certificate HighPrivCert

    ENCRYPTION BY PASSWORD = 'Str0ngPWD!'

    WITH SUBJECT = 'Certificate for signing stored procedures'

    select * from sys.certificates

    GO

    create login HighPrivCertLogin from certificate HighPrivCert -- create high priv login

    grant view server state to HighPrivCertLogin

    GO

    -- in order to grant db user rights, we must transfer to that db the same certificate

    backup 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

    )

    GO

    use TestCert

    create 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 certificate

    ADD SIGNATURE TO OBJECT::dbo.show_processes

    BY CERTIFICATE HighPrivCert

    WITH PASSWORD='Str0ngPWD!'

    GO

    execute as login='LowPriv'

    exec sys.sp_who2 -- shows only my session

    exec dbo.show_processes -- shows all sessions! We are running under HighPrivCertLogin which have VIEW SERVER STATE permission.

    revert

    GO

    -- CLEANUP

    /*

    use master

    drop database TestCert

    drop login HighPrivCertLogin

    drop certificate HighPrivCert

    drop login LowPriv

    */

    Enjoy!

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths
  • datwungai (3/19/2013)


    What exactly are you afraid of?

    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?

    Then you are taking the right stance if they are a former DBA - they now have no right to access (except though any application)- stick to your guns.

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • datwungai (3/19/2013)


    What exactly are you afraid of?

    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?

    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:

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Eugene, 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. 😉

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • Thanks Vedran!

    The certificate works like a charm!

    dwg

  • Thanks Stuart, my sentiments exactly. It's not the presidency, if you forfiet your title here, it's gone for good!

  • Well 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."

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply