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 12»»

Profiler shows LoginName as 'sa', but 'sa' is disabled on the instance. Expand / Collapse
Author
Message
Posted Friday, December 14, 2012 2:27 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, March 20, 2014 3:23 PM
Points: 51, Visits: 333
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?
Post #1396819
Posted Tuesday, December 18, 2012 7:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 3:26 AM
Points: 39, Visits: 487
'sa' account is the default sql admin user and all system processes 'below spid 50' uses sa

Regards
Post #1397777
Posted Tuesday, December 18, 2012 7:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 8:53 AM
Points: 5,218, Visits: 5,072
System processes are no longer limited to SPID < 50 they can go over 50 depending on the task they perform.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1397778
Posted Tuesday, December 18, 2012 7:45 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, March 20, 2014 3:23 PM
Points: 51, Visits: 333
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.
Post #1397803
Posted Tuesday, December 18, 2012 7:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 8:53 AM
Points: 5,218, Visits: 5,072
Do the procs have the EXECUTE AS clause to let them run under higher privileged accounts?



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1397806
Posted Tuesday, December 18, 2012 7:52 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 9:44 AM
Points: 42,822, Visits: 35,953
Impersonation? Got procs with EXECUTE AS 'sa' in them?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1397807
Posted Tuesday, December 18, 2012 8:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, March 20, 2014 3:23 PM
Points: 51, Visits: 333
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!
Post #1397817
Posted Tuesday, December 18, 2012 8:25 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 9:44 AM
Points: 42,822, Visits: 35,953
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 2008, MVP
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

Post #1397837
Posted Tuesday, December 18, 2012 8:42 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, March 20, 2014 3:23 PM
Points: 51, Visits: 333
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?
Post #1397849
Posted Tuesday, December 18, 2012 8:55 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 9:44 AM
Points: 42,822, Visits: 35,953
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 2008, MVP
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

Post #1397862
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse