SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Custom sp_who


Custom sp_who

Author
Message
datwungai
datwungai
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 247
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
jeetsingh.cs
jeetsingh.cs
Old Hand
Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)

Group: General Forum Members
Points: 339 Visits: 618
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
Stuart Davies
Stuart Davies
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5264 Visits: 4682
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
Eugene Elutin
Eugene Elutin
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4948 Visits: 5478
... 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
Vedran Kesegic
Vedran Kesegic
Say Hey Kid
Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)

Group: General Forum Members
Points: 708 Visits: 1266
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

Vedran Kesegic
Vedran Kesegic
Say Hey Kid
Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)

Group: General Forum Members
Points: 708 Visits: 1266
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

datwungai
datwungai
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 247
Thank you everyone!

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

DWG
datwungai
datwungai
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 247
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?
Vedran Kesegic
Vedran Kesegic
Say Hey Kid
Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)

Group: General Forum Members
Points: 708 Visits: 1266
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

Stuart Davies
Stuart Davies
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5264 Visits: 4682
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search