sp_who\who2 permisisons

  • Our developer is dbo of the database he works on. When he runs sp_who or who2 in that database (under Query editor) the only user returned is himself. Why can't he see others using accessing this database (he is the dbo of this database)

    Is there a speicfic permissons we can assign him, other than "sa"!, we can assign so he can see others accesing this database.

    TIA,

    barkingdog

  • Hi,

    You can grant him the VIEW SERVER STATE permission (as per BoL if you look up sp_who).

    HTH!

    /Elisabeth

    elisabeth@sqlserverland.com
    MCITP | MCT
    http://sqlblog.com/blogs/elisabeth_redei/
    http://linkedin.com/in/elisabethredei

  • In these cases, rather than grant any sort of elevated access, I write a wrapper stored procedure 'with execute as' a priviliged account. This way they can only see what you want them to see.

    http://msdn.microsoft.com/en-us/library/ms188354.aspx

    The reason he can't see anyone is because of metadata visibility in SQL 2005.

    He needs to 'own' things that he sees by default.

    http://msdn.microsoft.com/en-us/library/ms188354.aspx

    At the end of the day, that's probably the "right" way to set this up, but requires more fooling around than my 'execute as' stored procedure.

    In that case, you'd just return stuff from sysprocesses selected out by his database. or you could program it to look for any dbo or db_owner in all databases and allow them to see their own databases by using suser_id(), a cursor, and maybe even show the SQL from sys.dm_exec_sql_text by cross applying sql_handle.

    http://msdn.microsoft.com/en-us/library/ms181929.aspx

    good luck, have fun.

    ~BOT

  • That is actually not true.

    GRANT VIEW SERVER STATE TO

    is required for sp_who to make the grantee able to see all executing sessions.

    /Elisabeth

    elisabeth@sqlserverland.com
    MCITP | MCT
    http://sqlblog.com/blogs/elisabeth_redei/
    http://linkedin.com/in/elisabethredei

  • In my experience, setting a database to trustworthy then creating a proc 'with execute as sa' will allow the executor of the proc to see everything that sa can see.

    Maybe sp_who2 won't work right, but you can select from sysprocesses without the metadata visibility issues. I think sp_who2 shoudl work, though too.

    If the database is not set to trustworthy, metadata visibility rules still apply.

    alter database SQLAdmin set trustworthy on

    use sqladmin

    go

    create user [sa_access] from login [some_sysadmin_account]

    create proc dbo.usp_who2 with execute as 'sa_access'

    as

    exec sp_who2

    go

    create login [luser] with password ='luser222'

    go

    create user [luser] from login [luser]

    grant exec on dbo.usp_who2 to luser

    execute as login = 'luser'

    exec dbo.usp_who2

    revert

    ~BOT

  • bear in mind that the trustworthy database property is set to off by default. to safeguard from threats from malicious assemblies and malicious modules. which is funnily enough what you do.

    Malicious modules that are defined to execute as high privileged users. For more information, see Extending Database Impersonation by Using EXECUTE AS

    😀

    --------------------------------------------------------------------------------------
    [highlight]Recommended Articles on How to help us help you and[/highlight]
    [highlight]solve commonly asked questions[/highlight]

    Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
    Managing Transaction Logs by Gail Shaw[/url]
    How to post Performance problems by Gail Shaw[/url]
    Help, my database is corrupt. Now what? by Gail Shaw[/url]

Viewing 6 posts - 1 through 5 (of 5 total)

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