Profiler shows LoginName as 'sa', but 'sa' is disabled on the instance.

  • As the subject says, I have a profiler running and see some things executing as 'sa' in the LoginName field, but 'sa' on the server is disabled. I even changed its password.

    These aren't system level processes, as the NTUserName is an actual user, who is using the application that connects to the database on our dev environment. Any ideas on what's going on?

  • 'sa' account is the default sql admin user and all system processes 'below spid 50' uses sa

    Regards

  • System processes are no longer limited to SPID < 50 they can go over 50 depending on the task they perform.

  • They're not system processes, though. I see a NTUserName associated with the process and they're executing user stored procs called from a web app.

  • Do the procs have the EXECUTE AS clause to let them run under higher privileged accounts?

  • Impersonation? Got procs with EXECUTE AS 'sa' in them?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Aaaaah, thanks guys. I feel so dumb now. Why are these things always so obvious in hindsight?

    The procs were set to EXECUTE AS OWNER, and the database was owned by sa in our QA environment. Thanks a ton!

  • llevity (12/18/2012)


    The procs were set to EXECUTE AS OWNER, and the database was owned by sa in our QA environment. Thanks a ton!

    Execute as owner does not use the database owner, it uses the procedure owner. Means a sysadmin created the procedures, that's all.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Another thing to file in my "bad assumptions" filing cabinet.

    Another question, then. When I change the object owner of the proc via sp_changeobjectowner, it changes the schema from dbo to the new owner I specify. Is this a case where schema and owner are the same thing? And this EXECUTE AS OWNER proc is running as sa because it's in the dbo schema?

  • Schema != owner

    That proc is old (pre SQL 2005) and hence changing schema and owner, before SQL 2005 they were the same thing.

    Use ALTER AUTHORIZATION to change owner and ALTER SCHEMA to move a table to a new schema.

    Books Online:

    sp_changeobjectowner

    Changes the owner of an object in the current database.

    Important:

    This stored procedure only works with the objects available in Microsoft SQL Server 2000. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER SCHEMA or ALTER AUTHORIZATION instead. sp_changeobjectowner changes both the schema and the owner. To preserve compatibility with earlier versions of SQL Server, this stored procedure will only change object owners when both the current owner and the new owner own schemas that have the same name as their database user names.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Okay, again, so much clarity in hindsight.

    I also see another source of my confusion. Apparently, a stored proc is owned by whoever owns the schema it is in, by default, unless its specifically changed via ALTER AUTHORIZATION. So while schema != owner, the schema owner IS the procedure owner, unless it's been specifically changed.

    In my case, I'm seeing sa run these things because the proc is in the dbo schema, and the dbo schema is owned by dbo. I guess dbo = sa when it translates to login.

    More info on default ownership[/url]:

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply