User - select from master database query.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » User - select from master database query.

User - select from master database query.

Rate Topic

Display Mode

Topic Options

Author

Message

m.dunster

Posted Sunday, October 14, 2012 12:38 PM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 16, 2013 6:41 PM
Points: 81, Visits: 239

Hi,

I need to access the master table to checkl the applications logged into the database for a licensing issue.

If i run the following code

select * from master.dbo.sysprocesses

when logged in as the SA or a SQL admin, it shows me what I expect. If I use it in a basic user login context, it will only show me the row relating to that login.

The guest security is enabled on the Master DB.

Is there a good way to obtain this data with a baisc user login?

Thanks,

Matthew

Post #1372525

e4d4

Posted Sunday, October 14, 2012 2:15 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 2:42 PM
Points: 151, Visits: 1,036

The user must have view server state permission, to grant user that permission execute:

GRANT VIEW SERVER STATE TO yours_user;

http://msdn.microsoft.com/en-us/library/ms179881.aspx sys.sysprocesses (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms186717.aspx GRANT Server Permissions (Transact-SQL)

Post #1372534

m.dunster

Posted Monday, October 15, 2012 4:17 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 16, 2013 6:41 PM
Points: 81, Visits: 239

e4d4 (10/14/2012)

The user must have view server state permission, to grant user that permission execute:

GRANT VIEW SERVER STATE TO yours_user;

http://msdn.microsoft.com/en-us/library/ms179881.aspx sys.sysprocesses (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms186717.aspx GRANT Server Permissions (Transact-SQL)

Thanks e4d4.

All working

Post #1372639

« Prev Topic | Next Topic »

Permissions