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

Windows Login Removed but SUSER_SNAME() Still Works Expand / Collapse
Author
Message
Posted Monday, October 21, 2013 11:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 1:45 PM
Points: 38, Visits: 227
I removed certain Windows logins from all our SQL Servers (2000 - 2012) awhile ago. I am finding some maintenance plans are still owned by these logins. In trying to identify these and other items, I am finding the SIDs are still around in system tables (no surprise); however, running suser_sname() still returns the correct logins even though they've been removed. How is it able to correctly resolve it?

Thanks!
Post #1506762
Posted Monday, October 21, 2013 11:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:40 AM
Points: 7,001, Visits: 8,439
can you double check these windows logins aren't having login auth via granted windows groups?

Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1506775
Posted Monday, October 21, 2013 11:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 1:45 PM
Points: 38, Visits: 227
That is a very good point! I just verified on one of the SQL 2005 servers where I'm seeing this issue that the Windows account is NOT part of a group. I double-checked it is not in server_principals either.

Post #1506777
Posted Monday, October 21, 2013 12:15 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 1:45 PM
Points: 38, Visits: 227
These particular accounts do NOT exist in any groups, whether local or in Active Directory. However, they are AD accounts that have been disabled. Now I am wondering if SQL Server is somehow resolving the SID with the AD account itself. I just ran SUSER_SNAME for one of the SIDs in a query against all 60+ instances that range from SQL 2000 to 2012. Guess what? The same SID returns the same account on every single instance.
Post #1506785
Posted Monday, October 21, 2013 12:47 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:40 AM
Points: 7,001, Visits: 8,439
What has been the goal of you dropping the windows login from these instances ?

You may want to change the job-owner for these jobs to sa and/or execute their steps using a proxy if other windows auth is needed for those steps.

sp_blitz reports these non-sa owned jobs.

ref: http://www.brentozar.com/blitz/jobs-owned-by-user-accounts/


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1506804
Posted Monday, October 21, 2013 1:01 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:03 PM
Points: 4,388, Visits: 9,510
SUSER_SNAME() does check AD and will return the user information if found.

Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1506813
Posted Monday, October 21, 2013 3:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 1:45 PM
Points: 38, Visits: 227
Thank you! I plan on changing the owner of the maintenance plans and jobs to "sa". The original goal here was to find and update maintenance plans (with corresponding jobs) where the owner is an account belonging to former DBAs who no longer work here. I removed the logins months ago but when I update the maintenance plans they had created with their accounts, the job owner reverts back to their accounts. Then the jobs fail when they run.
Post #1506882
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse