June 1, 2017 at 7:56 am
Hello,
We have SQL user created with with DBO permissions for a specific db created for them. according to them they are unable to see all running queries which are running on that database.
can someone please help on this
Regards
June 1, 2017 at 8:31 am
atulyan.aries - Thursday, June 1, 2017 7:56 AMHello,We have SQL user created with with DBO permissions for a specific db created for them. according to them they are unable to see all running queries which are running on that database.
can someone please help on thisRegards
Depends on what they are using to view running queries but I would guess they can only see what their session is running. Queries run on the instance and not necessarily just in one particular database. Often what you are describing is due to not having view server state permissions but that sounds like granting more permissions than needed and everyone really shouldn't be able to see the text of everything running on the instance.
You may want to figure out more of what they want and create a stored procedure for them to use.
Sue
June 1, 2017 at 8:52 pm
Thank you for your reply, let me explain further...
When i run sp_who2 active I see all process, but when the other person runs sp_who2 active he gets to see only his SPID
Why is that
June 1, 2017 at 9:33 pm
What are your rights in the database vs the person who can see everything? Sounds like you need a stored procedure with EXECUTE AS.
But don't take my word for it, I'm an idiot.
June 1, 2017 at 10:02 pm
the person who can see all process is the DBA, while the other person who can only see his process logs in using SQL Authentication with dbo permissions
June 1, 2017 at 10:12 pm
Yep, sounds like a permissions issue.
Okay, found an article here that's even better than the one I originally posted...
Execute As article.
June 2, 2017 at 12:39 am
DBO gives full rights on a database, so you can do anything in that database, it does not give rights to the server as a whole.
Running queries are server-level, not database level. To see server-level information like that, you need, iirc, VIEW SERVER STATE permissions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 2, 2017 at 9:45 pm
what kind of permissions should i give him
June 4, 2017 at 1:43 am
GilaMonster - Friday, June 2, 2017 12:39 AMTo see server-level information like that, you need, iirc, VIEW SERVER STATE permissions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 4, 2017 at 8:41 am
GilaMonster - Friday, June 2, 2017 12:39 AMDBO gives full rights on a database, so you can do anything in that database, it does not give rights to the server as a whole.
Running queries are server-level, not database level. To see server-level information like that, you need, iirc, VIEW SERVER STATE permissions.
Yep, you recall correctly. VIEW SERVER STATE is what's required for sys.dm_exec_sessions and sys.dm_exec_requests.
June 4, 2017 at 2:47 pm
atulyan.aries - Friday, June 2, 2017 9:45 PMwhat kind of permissions should i give him
As I originally said, view server state and if this is only so a user can see everything by executing sp_who2 then you are most likely granting too many privileges. So I'd do the certificate signed stored procedure. And being that the permissions needed are server level permissions, you'd need to create a certificate in master, create a login mapped to that certificate, grant that cert login view server state, export and import the cert into the database your user is using and then sign the stored procedure with the certificate you created. It will execute under the permissions of the login mapped to the certificate that is used to signed the stored procedure.
It's not as bad as it sounds. Here is an example if the database is named Test and the login you want to grant permissions to is TestLogin. How original.
--In destination database, create stored procedure
USE Test
GO
CREATE PROCEDURE dbo.NewSP_who2
AS
EXEC sp_who2
RETURN
GO
--need to grant execute on the stored procedure to the user
GRANT EXECUTE ON dbo.NewSP_who2 TO TestLogin
--create cert in master so it can be mapped to a login
USE master
CREATE CERTIFICATE EscalateSPCert
ENCRYPTION BY PASSWORD = 'mibT4a$pwd2'
WITH SUBJECT = 'Certificate for signing stored procedures'
GO
--create login from certificate, grant view server state
CREATE LOGIN EscalateSPLogin FROM CERTIFICATE EscalateSPCert
GRANT VIEW SERVER STATE to EscalateSPLogin
GO
-- backup cert so you can create it from file in the destination database
BACKUP CERTIFICATE EscalateSPCert
TO FILE='C:\temp\EscalateSPCert.cer'
WITH PRIVATE KEY
( FILE = 'C:\temp\EscalateSPCert.pvk',
-- encryption key password
DECRYPTION BY PASSWORD = 'mibT4a$pwd2',
--new pwd for encrypted file,
--delete files when done in destination database
ENCRYPTION BY PASSWORD = 'yoqt&f3pD'
)
GO
--Use destination database with SP, create cert from file
USE Test
GO
CREATE CERTIFICATE EscalateSPCert
FROM FILE='C:\temp\EscalateSPCert.cer'
WITH PRIVATE KEY
( FILE = 'C:\temp\EscalateSPCert.pvk',
--file password
DECRYPTION BY PASSWORD = 'yoqt&f3pD',
--encryption key password
ENCRYPTION BY PASSWORD = 'mibT4a$pwd2'
)
GO
--***DELETE CERT FILES from C:\temp after creating in destination database above
-- Sign the sp so it runs under the login mapped to the certificate
ADD SIGNATURE TO OBJECT::dbo.NewSP_who2
BY CERTIFICATE EscalateSPCert
WITH PASSWORD='mibT4a$pwd2'
GO
--Test
EXECUTE AS LOGIN='TestLogin'
EXEC sys.sp_who2 -- shows only TestLogin session
EXEC dbo.NewSP_who2-- shows all sessions for sp_who2
REVERT
GO
Sue
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply