How can i detemine owner of SQL statement which is running ???

Question

Post reply

How can i detemine owner of SQL statement which is running ???

sol.nt

SSCrazy

Points: 2062
More actions
December 13, 2006 at 10:35 pm

#118243

Hi all
Can someone help me what to do to get info about account which is executing a sql statement when it running
Thanks

Viewing 13 posts - 1 through 12 (of 12 total)

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

MohammedU SSCoach Points: 19644 More actions · Answer 1

If you know the spid you use to query sysprocess table...

select loginame from sysprocesses where spid = ...

MohammedU
Microsoft SQL Server MVP

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

ChiragNS

One Orange Chip

Points: 26137

December 14, 2006 at 12:33 am

#678007

try to catch it in sql profiler

"Keep Trying"

sol.nt SSCrazy Points: 2062 More actions · Answer 3

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

Sergiy SSC Guru Points: 110192 More actions · Answer 4

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

sol.nt SSCrazy Points: 2062 More actions · Answer 5

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.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 6

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

Chris Chilvers SSCrazy Points: 2260 More actions · Answer 7

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.

R Michael SSCarpal Tunnel Points: 4211 More actions · Answer 8

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

Ronald San Juan SSCrazy Points: 2937 More actions · Answer 9

SELECT system_user

Ronald San Juan | SQL DBA
ID 710124 ~ Code "Northwind"

sol.nt SSCrazy Points: 2062 More actions · Answer 10

@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.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 11

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

sol.nt SSCrazy Points: 2062 More actions · Answer 12

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