need more detail than sp_who 1

[babeo]

Hi

1) sp_who does not give me more detail on what user accesses what table then. Is there any command, procedure or table that I can query to see a particular user accessing what table?

2) How to lock all users from accessing a database or table and how to unlock it ? Or is this need to be done on the OS level? If so how to do it on UNIX env?

3) How to lock out for a particular user? or is it can be done?

Thanks

 

[JMCraig]

RE what user is accessing what table, this only works in the instant that the user is doing an operation on the table (that is, most of the time it'll return no rows (unless you happen to catch operations that are currently underway so that there are locks on the tables in question). But if someone's running a long process, you can see what table's being accessed (and watch for progress by repeatedly running it to see what page the lock(s) are on).

So that you can see the table name in question, run it from the DB whose table's are of interest.

use <your db goes here>
go
select p.spid, p.cmd, p.status, tbl_name = object_name (l.id), l.page
from master..sysprocesses p, master..syslocks l
where p.spid = l.spid

RE locking DB's and Tables
&quot;Locking&quot; a DB can be done by either putting it into &quot;dbo use only&quot;, &quot;single-user&quot; mode or by putting it into &quot;read-only&quot; mode (as appropriate). All these are invoked via the sp_dboption command (see the Sys Admin Guide--for 12.5 it's in Chapter 22). The 12.5 version (I can't recall is 12.0 has this or not) also has a &quot;quiesce database&quot; which allows you to suspend updates (although it's discussed in the context of copying device files--for 12.5 this is in Chapter 26).

Hmmm. Locking tables is going to be a bit trickier unless all your users belong to a given group (e.g. public). Then you can do this:

revoke all on <table name> from <group name>

You could also, of course, just revoke UPDATE, DELETE, & INSERT permissions, for instance.

RE Locking a particular user, you need sp_locklogin (see the Sys Admin Guide's chapter on managing logins and users).

HTH,

John
J M Craig
Alpha-G Consulting, LLC

http://alphagconsulting.bizhosting.com

nsjmcraig@netscape.net

 

[babeo]

Thanks

That's really help me.

 

[JMCraig]

Another thing I forgot to mention, you can also set up auditing which will give you a lot of additional tracking options. See the Sys Admin Guide on auditing.

Best of luck,

John J M Craig
Alpha-G Consulting, LLC

http://alphagconsulting.bizhosting.com

nsjmcraig@netscape.net

 

[RayPeck]

Something I've also found helpful is this command:

exec sp_showplan spid, null, null, null

where you replace &quot;spid&quot; above with the number of the process id you see when you do an sp_who. This command will basically do an sp_showplan on the process that is running at that moment--so you can use it to see what table and what index are being accessed right then. This can be very handy in determining what process someone is running when they are blocking others, or whatever.

Ray