Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Custom sp_who Expand / Collapse
Author
Message
Posted Friday, March 15, 2013 3:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 3:01 PM
Points: 14, Visits: 162
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
Post #1431780
Posted Monday, March 18, 2013 12:38 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 11, 2014 3:38 AM
Points: 251, Visits: 613
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
Post #1432086
Posted Monday, March 18, 2013 2:28 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:42 AM
Points: 3,086, Visits: 3,282
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
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
Post #1432100
Posted Monday, March 18, 2013 3:13 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:55 AM
Points: 2,852, Visits: 5,107
... 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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1432112
Posted Monday, March 18, 2013 10:11 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 5:27 AM
Points: 369, Visits: 1,214
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/


_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Post #1432251
Posted Monday, March 18, 2013 10:25 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 5:27 AM
Points: 369, Visits: 1,214
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
Post #1432256
Posted Tuesday, March 19, 2013 2:39 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 3:01 PM
Points: 14, Visits: 162
Thank you everyone!

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

DWG
Post #1432900
Posted Tuesday, March 19, 2013 2:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 3:01 PM
Points: 14, Visits: 162
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?
Post #1432902
Posted Tuesday, March 19, 2013 6:30 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 5:27 AM
Points: 369, Visits: 1,214
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
Post #1432958
Posted Wednesday, March 20, 2013 2:21 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:42 AM
Points: 3,086, Visits: 3,282
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
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
Post #1433054
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse