December 14, 2012 at 2:27 pm
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?
December 18, 2012 at 7:03 am
'sa' account is the default sql admin user and all system processes 'below spid 50' uses sa
Regards
December 18, 2012 at 7:04 am
System processes are no longer limited to SPID < 50 they can go over 50 depending on the task they perform.
December 18, 2012 at 7:45 am
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.
December 18, 2012 at 7:51 am
Do the procs have the EXECUTE AS clause to let them run under higher privileged accounts?
December 18, 2012 at 7:52 am
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
December 18, 2012 at 8:00 am
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!
December 18, 2012 at 8:25 am
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
December 18, 2012 at 8:42 am
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?
December 18, 2012 at 8:55 am
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
December 18, 2012 at 9:13 am
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.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply