0

I need to monitor the local admins on my SQL Server systems for auditing. Is there a way to use a stored procedure with powershell to accomplish that?

3

You don't need to use Powershell for this, which would require xp_cmdshell in a stored procedure. Just use xp_logininfo. As long as you add the group to SQL Server (you don't have to give it any permissions), you can query the members of it.

EXEC master.dbo.xp_logininfo 'Builtin\Administrators', 'members'
1

You can use this stored procedure to collect the local admins on the system as long as it runs under the context of a member of the sysadmin role.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create procedure [dbo].[usp_CollectLocalAdmins]
as
-- list all members of Local Administrators group
set nocount on
declare @tsql nvarchar(1000)
create table #Output (output varchar(1000))
set @tsql=  'xp_cmdshell ''powershell -c "$([ADSI]''''WinNT://' + @@servername + '/Administrators,group'''').psbase.Invoke(''''Members'''') | foreach { $_.GetType().InvokeMember(''''ADspath'''', ''''GetProperty'''', $null, $_, $null).Replace(''''WinNT://'''', '''''''') }"'''

insert into #Output
    exec sp_executesql @tsql
select 
    @@servername ServerName,
    output MemberName 
from #Output o

drop table #output
go
  • 4
    Wouldn’t' it be easier just to use the powershell without it being inside a stored procedure? – James Z Aug 26 '16 at 20:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.