How to create a policy that will periodically check the membership of sysadmin?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » How to create a policy that will periodically...

How to create a policy that will periodically check the membership of sysadmin?

Rate Topic

Display Mode

Topic Options

Author

Message

KoldCoffee

Posted Sunday, July 04, 2010 6:27 PM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:17 AM
Points: 440, Visits: 995

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!!!!!!

Post #947360

Steve Jones - SSC Editor

Posted Sunday, July 04, 2010 7:17 PM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:26 PM
Points: 31,425, Visits: 13,738

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

Steve Jones - SSC Editor

Posted Sunday, July 04, 2010 7:18 PM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:26 PM
Points: 31,425, Visits: 13,738

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

KoldCoffee

Posted Sunday, July 04, 2010 7:28 PM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:17 AM
Points: 440, Visits: 995

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?

Post #947365

THE-FHA

Posted Tuesday, March 08, 2011 12:41 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 26, 2012 10:17 PM
Points: 167, Visits: 470

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

Jon.Morisi

Posted Friday, April 29, 2011 3:49 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 8:12 AM
Points: 275, Visits: 679

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

gokseninkorkmaz

Posted Tuesday, August 28, 2012 2:47 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, September 04, 2012 5:01 AM
Points: 3, Visits: 9

Can you explain what you wrote in 'field' part? The part starting with ExecuteSql...

Post #1350736

gokseninkorkmaz

Posted Tuesday, August 28, 2012 2:50 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, September 04, 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

Jon.Morisi

Posted Tuesday, August 28, 2012 11:56 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 8:12 AM
Points: 275, Visits: 679

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

gokseninkorkmaz

Posted Tuesday, September 04, 2012 5:12 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, September 04, 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 »

Permissions