July 22, 2011 at 7:43 am
Execute as user is made up? It actually does exist but my guess was that it couldn't be used when executing policies.
BOL reference:
July 22, 2011 at 7:47 am
Shark Energy (7/22/2011)
Am I doing this wrong? (QotD) Even if its a subject I don't know about I try and come up with an answer as its a test of my knowledge. I don't look up the answers beforehand.Does everyone else treat them like a real life scenario at work where you books online anything you dont know?
I using it as a learning opportunity. If it's something I think I know or be able to figure out from what I know I'll just answer. If I don't I'll either do research since I learn better that way than getting it put right in front of me or guess depending on my mood and the question.
July 22, 2011 at 7:54 am
crafdenberg (7/22/2011)
Execute as user is made up? It actually does exist but my guess was that it couldn't be used when executing policies.BOL reference:
Execute As (Transact-SQL)
Uh Oh... maybe I spoke to soon. I totaly missed the explination saying this was made up.
"Execute As" is definately a real thing! 😉
"Execute As" is also definately not a permission! :w00t:
You might be able to use this to test the Policy Alert if the User supplied has the ALTER TRACE permission. I realy have no way to test at this time.... 😎
Did you try this crafdenberg? If you can post some working script it would prove your point, but it would not make this command a permission. :hehe:
July 22, 2011 at 7:58 am
We don't use policies to manage SQL yet. I'm planning on testing it out and see if it's something we want to pursue but don't have time to do it right now.
July 22, 2011 at 8:32 am
cengland0 (7/22/2011)
Hey, wait a second. According to:http://technet.microsoft.com/en-us/library/bb510667.aspx
Administering Policy-Based Management requires membership in the PolicyAdministratorRole role in the msdb database. This role has complete control of all policies on the system. This control includes creating and editing policies and conditions and enabling and disabling policies.
So tell me why did I get it wrong?
As well as the decent explanation you've already been given, the option in the question was actually PolicyAdministrator rather than the correct role name of PolicyAdministratorRole
July 22, 2011 at 9:16 am
Kwex (7/22/2011)
Nice question! Got me researching on Alerts, Roles and Policies before I answered and got it right 🙂
Thanks - and good:-)
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 22, 2011 at 9:17 am
Thomas Abraham (7/22/2011)
Excellent question. Thanks Jason!
My pleasure
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 22, 2011 at 9:17 am
Nice question. Missed it and learned something. Thanks
July 22, 2011 at 9:17 am
Britt Cluff (7/22/2011)
Thanks for the question.
You're welcome
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 22, 2011 at 9:18 am
stewartc-708166 (7/22/2011)
This was quite a headscratcher.thanks, Jason, learned something today.
Woohoo - goal accomplished then
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 22, 2011 at 9:30 am
Good Question. Thanks! I over guessed it and should of stuck with my first thought.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
July 22, 2011 at 9:40 am
cengland0 (7/22/2011)
Hey, wait a second. According to:http://technet.microsoft.com/en-us/library/bb510667.aspx
Administering Policy-Based Management requires membership in the PolicyAdministratorRole role in the msdb database. This role has complete control of all policies on the system. This control includes creating and editing policies and conditions and enabling and disabling policies.
So tell me why did I get it wrong?
Two different facets of PBM. This question was in regards to alerting and the "minimium" security required to test an alert. Administering policies can be accomplished by membership in PolicyAdministratorRole, but to test alerts one must at a minimum have "Alter Trace" permissions.
Also, PolicyAdministratorRole does not appear to have Alter Trace permissions.
Try:
SELECT * FROM sys.fn_builtin_permissions ('server')
sp_helprotect NULL,'PolicyAdministratorRole'
Alter Trace is a "Server" class permission and is covered by the "Control Server" permission. So it may be misleading in the article to say that this role can fully administer PBM. What it really means is that this role can create and edit policies but not test alerts associated to those policies.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 22, 2011 at 9:42 am
sharath.chalamgari (7/22/2011)
i was not thinking about the least previlages and selected the sysadmin role. thanks for the good question.
You're welcome
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 22, 2011 at 9:43 am
cfradenburg (7/22/2011)
Execute as user is made up? It actually does exist but my guess was that it couldn't be used when executing policies.BOL reference:
Yeah - I worded that poorly. Made up for this question as simply an invalid choice.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 22, 2011 at 9:45 am
SanDroid (7/22/2011)
crafdenberg (7/22/2011)
Execute as user is made up? It actually does exist but my guess was that it couldn't be used when executing policies.BOL reference:
Execute As (Transact-SQL)
Uh Oh... maybe I spoke to soon. I totaly missed the explination saying this was made up.
"Execute As" is definately a real thing! 😉
"Execute As" is also definately not a permission! :w00t:
You might be able to use this to test the Policy Alert if the User supplied has the ALTER TRACE permission. I realy have no way to test at this time.... 😎
Did you try this crafdenberg? If you can post some working script it would prove your point, but it would not make this command a permission. :hehe:
But since the question is asking for minimum permission "execute as" is not a permission and thus an invalid choice. 'Execute as' is an execution context.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply