May 19, 2014 at 8:34 am
Hello,
In our company we have a server (sql server 2008R2) with many databases including databases from a third-party software. The third-party software partner wants permission to see their process (sp_who2) regarding their database ,but the sp_who2 requires view server state. With this permission, our partner is able to see not only the information regarding their database but our internal too. Our political rules doesn't allow this...
What could i do to allow our partner guys to see only the processes regarding their database? My shot was to create a procedure with execute as <sysadm_user> but it doesn't work.
Thanks since now,
Rafael Melo - BR
May 19, 2014 at 8:51 am
There maybe a better solution using Adam Machanic's WhoIsActive stored proc, he has a variable @show_system_spids so you can stop those appearing and you can filter by database. you could workoout the parameters needed then make a stored proc of that.
May 19, 2014 at 8:53 am
rafael_si (5/19/2014)
Hello,In our company we have a server (sql server 2008R2) with many databases including databases from a third-party software. The third-party software partner wants permission to see their process (sp_who2) regarding their database ,but the sp_who2 requires view server state. With this permission, our partner is able to see not only the information regarding their database but our internal too. Our political rules doesn't allow this...
What could i do to allow our partner guys to see only the processes regarding their database? My shot was to create a procedure with execute as <sysadm_user> but it doesn't work.
Thanks since now,
Rafael Melo - BR
Not sure what you mean by "My shot was to create a procedure with execute as <sysadm_user> but it doesn't work.". I would think this is exactly what you would want to do. I would think you could create a procedure that would insert the results of sp_who2 into a temp table and then select from that temp table with whatever where predicates you need to return only the data you want them to see.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 19, 2014 at 9:01 am
Sean,
I've done that! I've create a stored procedure with execute as <sysadmin> user. But it is showing just the information about that user, not all connection information. Why does it occurs since i'm executing with a privileged user?
p.s: sorry my English. I'm trying to improve.
Regards,
Rafael
May 19, 2014 at 9:12 am
rafael_si (5/19/2014)
Sean,I've done that! I've create a stored procedure with execute as <sysadmin> user. But it is showing just the information about that user, not all connection information. Why does it occurs since i'm executing with a privileged user?
p.s: sorry my English. I'm trying to improve.
Regards,
Rafael
Without seeing the actual code you tried it is impossible to tell. See if this will work for you.
create procedure GetFilteredWho
(
@login varchar(max)
) WITH EXECUTE AS 'SomeUser'
as
create table #Who
(
SPID INT,
[Status] VARCHAR(MAX),
[Login] VARCHAR(MAX),
HostName VARCHAR(MAX),
BlkBy VARCHAR(MAX),
DBName VARCHAR(MAX),
Command VARCHAR(MAX),
CPUTime INT,
DiskIO INT,
LastBatch VARCHAR(MAX),
ProgramName VARCHAR(MAX),
SPID2 INT,
REQUESTID INT
)
INSERT INTO #Who EXEC sp_who2
SELECT *
FROM #Who
where [Login] = @login
drop table #who
go
exec GetFilteredWho 'YourUserNameToFind'
exec sp_who2
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 19, 2014 at 10:56 am
I've done right as you showed. What happens is that the proc show just the "SomeUser" session. What i've is:
1 - create "SomeUser" login with "View Server State" permission
2 - create the sp as you've showed with execute as "SomeUser", with no filter.
3 - grant execute on this sp to a user with NO "View Server State"
4 - execute the procedure with this user
But the result of the procedure shows just the "SomeUser" session. Not all session since "SomeUser" has privilege to see all sessions.
Sorry if i'm not clear.
Rafael
May 19, 2014 at 11:00 am
rafael_si (5/19/2014)
I've done right as you showed. What happens is that the proc show just the "SomeUser" session. What i've is:1 - create "SomeUser" login with "View Server State" permission
2 - create the sp as you've showed with execute as "SomeUser", with no filter.
3 - grant execute on this sp to a user with NO "View Server State"
4 - execute the procedure with this user
But the result of the procedure shows just the "SomeUser" session. Not all session since "SomeUser" has privilege to see all sessions.
Sorry if i'm not clear.
Rafael
SomeUser should be a user with the permissions required to view whatever sessions you want to see. The point of using execute as is it allows the proc to execute within the context of a defined user.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 19, 2014 at 1:13 pm
Hello,
I've found the possible solution. I saw that the permission to see all the session "view server state" is applying to a login, not to an user, and the "execute as" condition is applied to an user. So, I used the concept of "certificate" and now it is working properly.
I've followed this paper: http://blog.sqlxdetails.com/procedure-with-execute-as-login/
Thanks for your help and time!
Regards,
Rafael Melo
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply