March 24, 2022 at 3:17 pm
In our non-production environments, the developers and QA people use Extended Events frequently to capture exactly what is executing. On-prem, the only permissions required are VIEW SERVER STATE. That level of access is granted to the non-production environments, we are comfortable with that.
However, in Azure SQL Databases, they need to be able to do the same things. Azure Data Studio has an extended events extension that will serve the purpose perfectly. View server state does not exist in Azure SQL database, and view database state does not grant enough perms to run an extended event.
As far as I can tell, you need to be granted CONTROL permissions to be able to do this. That's a higher level of access that we want to grant to the various users.
Has anyone done this, and what perms allow access that are not as great as control?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 25, 2022 at 8:28 am
Docs state this: "... ALTER ANY DATABASE EVENT SESSION permission in the database."
Did you test that ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 25, 2022 at 1:45 pm
Docs state this: "... ALTER ANY DATABASE EVENT SESSION permission in the database."
Did you test that ?
I ran through a series of grant perms and none of them worked. I thought that was the first permission I tried.
This morning, after reading your answer, I went through a test with one of the QA folks. It failed, I granted alter any event to her group, and it worked. Thanks!
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 25, 2022 at 1:55 pm
BWAAA-HAAA-HAAA!!! (Sorry... couldn't help the laugh).
--Jeff Moden
Change is inevitable... Change for the better is not.
March 25, 2022 at 2:20 pm
BWAAA-HAAA-HAAA!!! (Sorry... couldn't help the laugh).
Yeah. There was a very large "DUUUUUUUUUUUUUUUUUHHHHHHHHHHHHH" this morning.
After I replied to the Johan, I figured out what my stupidity was. There are only two groups who are granted access to any environment, XXX-Readers and XXX-Writers. You have to request to be added to these groups, and it only lasts a certain number of days. I had a test account I was using. Guess what. That account was not a member of either of these groups. It's access had expired, and since there's no email for that account, nobody knew.
I used an actual real live person as a test, which is when it worked.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 25, 2022 at 4:02 pm
To be sure, my laugh wasn't about your bit of misfortune. I was laughing because it appears to be yet another limitation of Azure. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
March 25, 2022 at 5:00 pm
To be sure, my laugh wasn't about your bit of misfortune. I was laughing because it appears to be yet another limitation of Azure. 😀
Actually, it works in the same manner as on-prem. BUT. There's no "local storage", so you need to create a storage container for the .xel files.
I stumbled upon the extension for Azure Data Studio, which is strangely called "Profiler", but under the covers it's extended events. Almost all of the folks who need this just let the SQL scroll by, and stop it when they "see something". It works just fine.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 26, 2022 at 1:56 pm
Jeff Moden wrote:To be sure, my laugh wasn't about your bit of misfortune. I was laughing because it appears to be yet another limitation of Azure. 😀
Actually, it works in the same manner as on-prem. BUT. There's no "local storage", so you need to create a storage container for the .xel files.
I stumbled upon the extension for Azure Data Studio, which is strangely called "Profiler", but under the covers it's extended events. Almost all of the folks who need this just let the SQL scroll by, and stop it when they "see something". It works just fine.
Thanks. I'll have to check it out.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 28, 2022 at 6:39 am
Keep in mind: Not just any kind of Azure BLOB storage! It needs to be V2 ! ( or newer )
And you have to remove these files yourself ! ( on-prem SQL Server rolls over a specified xevent number of files )
Check my article "Logon monitoring in SQL Server and Azure Managed Instances - Adopting Extended Events"
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 28, 2022 at 12:07 pm
Keep in mind: Not just any kind of Azure BLOB storage! It needs to be V2 ! ( or newer )
And you have to remove these files yourself ! ( on-prem SQL Server rolls over a specified xevent number of files )
Check my article "Logon monitoring in SQL Server and Azure Managed Instances - Adopting Extended Events"
I already read your article, it is very good. It was what I was using as a guide. At some point I am going to have to set it up in this manner. I use extended events for a variety of different reasons on-prem. I need to gather the same info in Azure.
Right now, the extension for Azure Data Studio serves the purpose for the QA and dev folks.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 28, 2022 at 2:12 pm
Maybe have a look at Gianluca's WorkloadTools to process the xe event files in bulk
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 11, 2022 at 8:08 pm
We can use the SQL Server Management Studio (SSMS) to create an SQL Server extended events session. At first, we expand the Management folder and right-click on the Sessions. On the menu, we click the New Session… option to create a new extended event session, and then the New Session window will appear
Extended events is a lightweight performance monitoring system that enables users to collect data needed to monitor and troubleshoot problems in SQL Server.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply