April 25, 2012 at 10:21 am
I am trying to validate logins via policy-based management. I have an executesql where I create a table variable and run sp_validatelogins. I query the count of the table and if greater than zero there are bad logins. The problem is that I am using the EPM Framework code and the evaluated object doesn't return the invalid logins. This is because I am using the Server facet, which is on the server level. I would like to use the Logins facet and each login is returned in the field @Name. The problem is when I use the @Name field inside of my EXECUTESQL() it tells me that I have to DECLARE the scalar function @Name. Does anyone know how I can get the @Name value into my EXECUTESQL() statement?
April 25, 2012 at 12:40 pm
Without some context of your dynamic sql it is shooting in the dark. Can you post some details about what you are trying? At least the code you are running.
_______________________________________________________________
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/
April 25, 2012 at 6:39 pm
If you want to get a value returned to a local variable, you'll probably need to use sp_executesql:
http://msdn.microsoft.com/en-us/library/ms188001.aspx
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 26, 2012 at 6:29 am
Sorry, you are correct I should have sent an example. Here is the code that goes into the policy based management expression field of the condition:
ExecuteSql('Numeric', 'SELECT Isnull(SUSER_SID(@Name),0)')
As I stated previously, in this expression field there are many variables that correspond to diffierent items depending upon which facet you choose. @Name is the server, using the Server facet, the login, if using the login facet and so forth. But if I use the executesql() function and place the variable inside the function it then feels that it is a varibale of the EXECUTESQL() and wants it declared instead of passing the value from the facet.
April 26, 2012 at 7:36 am
I am not too knowledgeable about PBM but I will ping the others. Somebody will know this.
_______________________________________________________________
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/
April 26, 2012 at 3:50 pm
My first thought was to check the Login facet @sid property to see if it was NULL, but that turned out to be harder than it should have been. Then I found this:
Policy condition to allow check for NULL values
The item says it was fixed in Katmai, but I am using 2008R2 + SP1 + CU4 and it is not fixed, at least not in the UI. I may toy around with it some more, but for now it appears to be a non starter.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply