How can i detemine owner of SQL statement which is running ???
-
Hi all
Can someone help me what to do to get info about account which is executing a sql statement when it running
Thanks
-
If you know the spid you use to query sysprocess table...
select loginame from sysprocesses where spid = ...
MohammedU
Microsoft SQL Server MVP -
try to catch it in sql profiler
"Keep Trying"
-
My purpose is use username of who is running this statement to perform somethings such as Insert , Update on table which i create early which that username as owner so i need to know who is exec it
To: Chirag , sql profiler doesn't fix my purpose. Thanks anyway
-
If you connect to SQL Server as "sa" then the only name you can catch is "sa". This is the user running queries.
If you want to know the user name logged into application then you need to catch it in the application and passit to SP as a parameter.
_____________
Code for TallyGenerator -
i know if i use Windows authorization then tablw or view will be attached with my account.
but what do you mean when you said i should catch what user enter into my app , because my app have list account complete different with account of my domain and ownweship only attach with account of domain.
What i want is have any way to retrieve info of accoutn when thay are running sql statements such as what sql profiler do.
Regard.
-
If the users using your app are able to do a trusted logon to SQL Server by their domain/username, then it's most liekly that the app also could get this info, and, as Sergiy suggested, pass that as a parameter to SQL Server.
/Kenneth
-
what about the USER_NAME() function? This works in 2005, unfortunatly I don't have 2000 to check it on.
This will return the user name of the currently connected user, eg. 'sa', or 'domain\user name' depending on how they connected.
-
If I am reading your comment correctly then you are saying you log in as SA so that tables and views are always owned by dbo, and that if you log in using your windows credentials, the objects are owned by your user?
If this is correct, than you can fix this by qualifying objects when you create them. By qualifying, I mean specifying the owner of the object in the name, for example:
CREATE TABLE dbo.MyTable ( MyCol VARCHAR(30) )If you prepend dbo. to all your object names, that won't be a problem anymore.
SQL guy and Houston Magician
-
SELECT system_user
-
@To Ronald San Juan:
-"Select system_user" : Thanks , i will try to see what i can do with it
.-Add prefix dbo to objects when create: it right but i don't think it fix to my purpose. Anyway, thanks.
@To Chris Chilvers: USER_NAME() working the same way in SQL 2000
.@To Kenneth Wilhelmsson : i am look forward a different solution to solve this problem in SQL 2000, not on my App.
-
Ok, so you have your app, which is most likely to be aware of which domain/user that is currently using it, but you won't bother to pick up that info in your app, but you'd rather have some magic way for the recipient end on the other side of the wire (eg SQL Server) to be able to figure out something that you could provide when connecting..?
I'm also a little unclear how this app actually connects. Does it login as 'sa' (or some other generic user) or does it login as the domain user?
/Kenneth
-
Well , i think this is the worse security but my app let the clients connect to server which their account which they use to log in computer, i mean windows account, but when they create table , i want that table will have prefix with their account and i don't want to take that info from my app. So , i think use system_user or user_name() function is the best.
Regards
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply