Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to create a policy that will periodically check the membership of sysadmin? Expand / Collapse
Author
Message
Posted Sunday, July 4, 2010 6:27 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 12:20 AM
Points: 605, Visits: 1,412
Specifically, I need to know how to set up the condition. In other words, which facet and properties do I use to set up such a policy? Thank you!!!!!!



it helps to talk it out
Post #947360
Posted Sunday, July 4, 2010 7:17 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 8:48 AM
Points: 33,089, Visits: 15,198
Please put your question in the post, not the subject. It cuts off and can't be read.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #947362
Posted Sunday, July 4, 2010 7:18 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 8:48 AM
Points: 33,089, Visits: 15,198
Sorry, you also need to define what you mean by "check"? How does SQL Server know when the policy fails?






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #947363
Posted Sunday, July 4, 2010 7:28 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 12:20 AM
Points: 605, Visits: 1,412
I am going straight out of the MS SQL Server 2008 Training Kit, chapter review for Policy Based Management. Suggested Practice:

"Configure policies to checkthe membership of the sysadmin and db_owner roles".

In this context does this make more sense?


it helps to talk it out
Post #947365
Posted Tuesday, March 8, 2011 12:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 30, 2014 2:16 AM
Points: 180, Visits: 527
Has anyone has a solution to this question as i also need some tutorials on Policy based management on sql 2008.
please guys.
Post #1074619
Posted Friday, April 29, 2011 3:49 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 12:54 PM
Points: 293, Visits: 814
similar here
http://www.sqlservercentral.com/Forums/Topic1100990-1550-1.aspx?Update=1

I blogged about what I did to solve the problem here
http://jonmorisissqlblog.blogspot.com/2011/04/configure-policy-to-checks-that.html
Post #1101084
Posted Tuesday, August 28, 2012 2:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 4, 2012 5:01 AM
Points: 3, Visits: 9
Can you explain what you wrote in 'field' part? The part starting with ExecuteSql...
Post #1350736
Posted Tuesday, August 28, 2012 2:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 4, 2012 5:01 AM
Points: 3, Visits: 9
and I think, these code returns all sysadmin groups. I also need to get some of the sysadmin groups. For example I want to check if one desired group is sysadmin or not.
Post #1350738
Posted Tuesday, August 28, 2012 11:56 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 12:54 PM
Points: 293, Visits: 814
It's a SQL statement in the field textbox, Google ExecuteSQL. The select statement is:
SELECT serverroles.name
FROM sys.server_principals AS serverroles
JOIN sys.server_role_members serverrolemembers
ON serverrolemembers.role_principal_id = serverroles.principal_id
JOIN sys.server_principals serverrolemember
ON serverrolemembers.member_principal_id =
serverrolemember.principal_id
WHERE serverrolemember.name = 'BUILTIN\Administrators'

You can change the where clause if your interested in more than just the 'BUILTIN\Administrators' group.
Post #1351159
Posted Tuesday, September 4, 2012 5:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 4, 2012 5:01 AM
Points: 3, Visits: 9
Now I have another question. I want to check if default port is 1433 or not. If so, when I evaluate the condition I want to see "x" in target details part.. However, I could not create condition, it gives an error "make sure string constants are enclosed in single quotes and facet properties are prefixed with "@" sign." and I can not click "ok".

How do you think I can solve this problem?

And here it is the script I've been tring to create condition.

ExecuteSql('Numeric','declare @Server as varchar(128)
declare @KeyToInterogate as varchar(200)
declare @Version as varchar (512)
declare @PortNumber as varchar(8)
set @Server = @@ServerName
set @Version = left(@@Version, 38)
set @KeyToInterogate = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP'
if charindex('\',@@ServerName) > 0
begin
set @KeyToInterogate = 'SOFTWARE\Microsoft\Microsoft SQL Server\'
set @KeyToInterogate = @KeyToInterogate + substring(@@ServerName,charindex('\',@@ServerName) + 1,len(@@ServerName) - charindex('\',@@ServerName))
set @KeyToInterogate = @KeyToInterogate + '\MSSQLServer\SuperSocketNetLib\Tcp'
end
exec xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = @KeyToInterogate,
@value_name = 'TcpPort',
@value = @PortNumber output
select @PortNumber
')
Post #1353819
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse