dropping sql logins from sysadmin.

Last Post 29 Sep 2012 06:20 PM by rm. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
new2sql
New Member
New Member

--
28 Sep 2012 03:09 PM
ok i know it's probably my inexperience, but... i have these 2 scripts.

one is a basic select with a concat results which automatically drop the sql login from sysadmin.

the other one is the same script packaged into a coalesce statement so i can run it with
exec (@dropfromsysadmin)

one script includes the 'sa', and the other does not. weird right? what am i forgetting?

/*************/

-- basic select ( shows the sa - normal stuff )
set nocount on
select 'exec master..sp_dropsrvrolemember ''' + name + ''',' + ' ''sysadmin''; '
from master..syslogins where sysadmin = '1'
and name not in ('NT SERVICE\SQLSERVERAGENT','NT SERVICE\MSSQLSERVER','NT AUTHORITY\SYSTEM')
go

/*************/

-- basic select with coalesce ( does not show the sa - weird )
declare @dropfromsysadmin varchar(2000)
select @dropfromsysadmin =
coalesce (@dropfromsysadmin + 'exec master..sp_dropsrvrolemember ''' + name + ''',' + ' ''sysadmin''; ' + char(10), '')
from master..syslogins
where sysadmin = '1'
and name not in ('NT SERVICE\SQLSERVERAGENT','NT SERVICE\MSSQLSERVER','NT AUTHORITY\SYSTEM')

select (@dropfromsysadmin)

/*************/
rm
New Member
New Member

--
29 Sep 2012 06:20 PM
Don't touch sa.


Acceptable Use Policy
---