grant a user permission to Master db

[Glowworm27]

I want to write a program that will query the Master.dbo.SysProcesses table to look for blocked and open_trans so I can send emails to the dba's that processes are being blocked.

however the account that I have setup does not have access to that table. I have tried allowing them access to the table and have even gone as far as making them a sqsadmin, but I keep getting the login failed for that user error message.

any help?

Thanks in advance.


George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net

 

[ptheriault]

I'm a little surprised that your DBA's already aware of blocking. Most DBA's have alerts setup to notify them of blocked or long running proccesses.

But you could try this...

Can you account run sp_who2? you can run this then just query your table variable.

DECLARE @tmpUsers TABLE(
spid int,
STATUS varchar(50),
loginname varchar(50),
hostname varchar(50),
blk varchar(10),
dbname varchar(50),
cmd varchar(30),
CPUTIME varchar(255),
DISKIO varchar(255),
LASTBATCH Varchar(30),
ProgramName Varchar(255),
SPID2 int,
requestid int)

INSERT INTO @tmpUsers EXEC SP_WHO2

SELECT *
FROM @tmpUsers

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[Glowworm27]

Three things,

First thanks! I didnt know about sp_who2

Second: there is an error that cannot use execute to populate a table variable

Server: Msg 197, Level 15, State 1, Line 18
EXECUTE cannot be used as a source when inserting into a table variable.

third: it does not tell me everything I want to know....



George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net

 

[ptheriault]

Your still on SQL 2000. You will have to use a temp table.
What else do you need to know that is not in sp_who2?

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[Glowworm27]

I want to be able to count the blocked and open_tran trans, then if I have more than one, query the table and return the rows to the program so it will send an email telling the dba's what is causing the problem and what is being held up....

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net

 

[ptheriault]

You can query that temp table anyway you want.

CREATE TABLE #tmpUsers (
spid int,
STATUS varchar(50),
loginname varchar(50),
hostname varchar(50),
blk varchar(10),
dbname varchar(50),
cmd varchar(30),
CPUTIME varchar(255),
DISKIO varchar(255),
LASTBATCH Varchar(30),
ProgramName Varchar(255),
SPID2 int,
requestid int)

INSERT INTO #tmpUsers EXEC SP_WHO2

SELECT * from #tmpUSers

SELECT COUNT(*) from #tmpUsers
WHERE blk >'-'

SELECT COUNT(*) from #tmpUsers
WHERE STATUS = 'RUNNABLE'

DROP TABLE #tmpUsers

You can even return the offending query if you know this spid.

declare @handle binary(20)
select @handle = sql_handle 
from master.dbo.sysprocesses where spid = [b]SPID[/b]
select * from ::fn_get_sql(@handle)

- Paul

- If at first you don't succeed, find out if the loser gets anything.