November 6, 2008 at 8:39 pm
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
November 7, 2008 at 6:46 am
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
November 8, 2008 at 8:13 am
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
Craig Outcalt
November 8, 2008 at 9:23 am
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
August 17, 2009 at 8:53 am
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
Craig Outcalt
August 19, 2009 at 9:31 am
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