October 21, 2013 at 11:13 am
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!
October 21, 2013 at 11:41 am
can you double check these windows logins aren't having login auth via granted windows groups?
Johan
Learn to play, play to learn !
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[/url]
- 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
October 21, 2013 at 11:50 am
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.
October 21, 2013 at 12:15 pm
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.
October 21, 2013 at 12:47 pm
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
Learn to play, play to learn !
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[/url]
- 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
October 21, 2013 at 1:01 pm
SUSER_SNAME() does check AD and will return the user information if found.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 21, 2013 at 3:00 pm
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply