SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Windows Login Removed but SUSER_SNAME() Still Works


Windows Login Removed but SUSER_SNAME() Still Works

Author
Message
sqlprincess
sqlprincess
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 233
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!
ALZDBA
ALZDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12059 Visits: 8923
can you double check these windows logins aren't having login auth via granted windows groups?

Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- 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 :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
sqlprincess
sqlprincess
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 233
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.
sqlprincess
sqlprincess
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 233
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.
ALZDBA
ALZDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12059 Visits: 8923
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


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- 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 :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7645 Visits: 9966
SUSER_SNAME() does check AD and will return the user information if found.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

sqlprincess
sqlprincess
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 233
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search