<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 - General
»
Profiler shows LoginName as 'sa', but 'sa' is...
11 posts, Page 1 of 2
1
2
»»
Profiler shows LoginName as 'sa', but 'sa' is disabled on the instance.
Rate Topic
Display Mode
Topic Options
Author
Message
llevity
llevity
Posted Friday, December 14, 2012 2:27 PM
Valued Member
Group: General Forum Members
Last Login: Friday, May 10, 2013 8:38 AM
Points: 51,
Visits: 322
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
Nchax Phoku
Nchax Phoku
Posted Tuesday, December 18, 2012 7:03 AM
SSC Rookie
Group: General Forum Members
Last Login: 2 days ago @ 7:32 AM
Points: 32,
Visits: 390
'sa' account is the default sql admin user and all system processes 'below spid 50' uses sa
Regards
Post #1397777
anthony.green
anthony.green
Posted Tuesday, December 18, 2012 7:04 AM
SSCertifiable
Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075,
Visits: 4,831
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
llevity
llevity
Posted Tuesday, December 18, 2012 7:45 AM
Valued Member
Group: General Forum Members
Last Login: Friday, May 10, 2013 8:38 AM
Points: 51,
Visits: 322
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
anthony.green
anthony.green
Posted Tuesday, December 18, 2012 7:51 AM
SSCertifiable
Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075,
Visits: 4,831
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
GilaMonster
GilaMonster
Posted Tuesday, December 18, 2012 7:52 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 37,635,
Visits: 29,886
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
llevity
llevity
Posted Tuesday, December 18, 2012 8:00 AM
Valued Member
Group: General Forum Members
Last Login: Friday, May 10, 2013 8:38 AM
Points: 51,
Visits: 322
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
GilaMonster
GilaMonster
Posted Tuesday, December 18, 2012 8:25 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 37,635,
Visits: 29,886
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
llevity
llevity
Posted Tuesday, December 18, 2012 8:42 AM
Valued Member
Group: General Forum Members
Last Login: Friday, May 10, 2013 8:38 AM
Points: 51,
Visits: 322
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
GilaMonster
GilaMonster
Posted Tuesday, December 18, 2012 8:55 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 37,635,
Visits: 29,886
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 »
11 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
