Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Query for SMS

Posted on 2008-10-27
11
Medium Priority
?
1,559 Views
Last Modified: 2013-11-21
I want to run a report in SMS to display PC Name, model, processor, memory, and service tag from Dell.  I found the following SQL query, but it errors out;

SELECT DISTINCT Computer_System_DATA.Name0 'Computer Name',
        Computer_System_DATA.Manufacturer00 'Manufacturer',
        Computer_System_DATA.Model0 'Model',
               processor_data.Name0 'Processor',
        PC_BIOS_DATA.serialNumber00 'Serial Number',
        PC_Memory_DATA.TotalPhysicalMemory0 'Memory'    
FROM Computer_System_DATA
LEFT OUTER JOIN processor_data ON Computer_System_DATA.machineID = processor_data.MachineID
LEFT OUTER JOIN PC_BIOS_DATA ON Computer_System_DATA.machineID = PC_BIOS_DATA.MachineID
LEFT OUTER JOIN PC_Memory_DATA ON Computer_System_DATA.machineID = PC_MEMORY_DATA.MachineID


The error I get after running the report is;

The SELECT permission was denied on the object 'PC_Memory_DATA', database 'ServerName', schema 'dbo'.
Error Number: -2147217911
Source: Microsoft OLE DB Provider for SQL Server
Native Error: 229



I know nothing about SQL, so baby talk would be great.  Also, that query shows Manufacturer and I don't need that and would rather have Username info, so any tips for changing that and fixing error would be appreciated, thanks.
0
Comment
Question by:JasperIAM
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
11 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22816906
>The SELECT permission was denied on the object 'PC_Memory_DATA', database 'ServerName', schema 'dbo'.

that means that the login you use to connect to the database does not have the (select) permission on that table.
you will have to grant some permissions to your login, easiest will be to add the db_datareader role in the database.
0
 
LVL 7

Author Comment

by:JasperIAM
ID: 22816997
How do I edit those permissions?  I assume thats in SQL?

Thanks.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22817155
> I assume thats in SQL?
you could use a SQL statement for it, or use the sql server management studio's gui.
what do you prefer to use?
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 7

Author Comment

by:JasperIAM
ID: 22817170
Whichever is easier for you to explain.....HA....New territory for me.....SQL newbie....

Thanks.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22817245
then, use the GUI.
go to the database for SMS, click on the security -> users folder, and open the properties of the user.
put the checkbox near the db_datareader role, and confirm the change.

after that, your query should not fail due to a select permission problem.
0
 
LVL 7

Author Comment

by:JasperIAM
ID: 22831065
Ok, so I couldn't find the GUI for SQL on my SMS box so I connected to the SQL database from a remote SQL server.  I then went to Security-Logins-MyUsername, properties, then "user mapping", checked db_datareader, hit OK.  I then get this error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Add member failed for DatabaseRole 'db_datareader'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Add+member+DatabaseRole&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Cannot use the special principal 'dbo'. (Microsoft SQL Server, Error: 15405)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=15405&LinkId=20476

------


I also found a spot that says my SMS server is running SQL Server Express Edition, if that helps.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22831100
that would mean that you try to grant some permissions to the dbo user?
0
 
LVL 7

Author Comment

by:JasperIAM
ID: 22831134
The only user I edited was mine.  Does that indicate my user is dbo user?  If so, what does that mean?  Does that mean i should be able to run the report in SMS?

Once again, I have no clue about SQL...and really just getting into the SMS.

Thanks.
0
 
LVL 7

Author Comment

by:JasperIAM
ID: 22831357
On another note, I can run other reports in SMS.  Its only when I try to create a specific report that it bombs out.
0
 
LVL 7

Author Comment

by:JasperIAM
ID: 22842609
How about another angle?  

Can you edit the following query?  So far, I've been able to edit it so it lists all PC's and not just one, but I would like to drop a few things, and add Service tag info.
 
Please drop User Domain, Computer Domain, Version of OS, and Manufacturer of Processor.  Then, please add Service Tag info, which is "SerialNumber0" from "v_GS_PC_BIOS".  Would like that next to PC Name.
 
I've tried, but I keep blowing it up.
 
Thanks.
 
 
SELECT SYS.Netbios_Name0, SYS.User_Name0, SerialNumber0,  SYS.Resource_Domain_OR_Workgr0,
  OPSYS.Caption0 as C054, OPSYS.Version0,
 MEM.TotalPhysicalMemory0,  IPAddr.IP_Addresses0, Processor.Manufacturer0,
 CSYS.Model0, Processor.Name0, Processor.MaxClockSpeed0  
FROM v_R_System SYS
LEFT JOIN  v_RA_System_IPAddresses IPAddr on SYS.ResourceID = IPAddr.ResourceID
LEFT JOIN  v_GS_X86_PC_MEMORY MEM on SYS.ResourceID = MEM.ResourceID
LEFT JOIN  v_GS_COMPUTER_SYSTEM CSYS on SYS.ResourceID = CSYS.ResourceID
LEFT JOIN  v_GS_PROCESSOR Processor  on Processor.ResourceID = SYS.ResourceID
LEFT JOIN v_GS_OPERATING_SYSTEM OPSYS on SYS.ResourceID=OPSYS.ResourceID
ORDER BY SYS.Netbios_Name0, SYS.Resource_Domain_OR_Workgr0
0
 
LVL 7

Accepted Solution

by:
JasperIAM earned 0 total points
ID: 22885778
Was able to get it to work.  Below is the correct statement.

SELECT SYS.Netbios_Name0, serialnumber0, SYS.User_Name0, OPSYS.Caption0 as C054, MEM.TotalPhysicalMemory0, CSYS.Model0, Processor.Name0, Processor.MaxClockSpeed0
FROM v_R_System SYS
LEFT JOIN  v_RA_System_IPAddresses IPAddr on SYS.ResourceID = IPAddr.ResourceID
LEFT JOIN  v_GS_X86_PC_MEMORY MEM on SYS.ResourceID = MEM.ResourceID
LEFT JOIN  v_GS_COMPUTER_SYSTEM CSYS on SYS.ResourceID = CSYS.ResourceID
LEFT JOIN  v_GS_PROCESSOR Processor  on Processor.ResourceID = SYS.ResourceID
LEFT JOIN v_GS_OPERATING_SYSTEM OPSYS on SYS.ResourceID=OPSYS.ResourceID
LEFT JOIN v_GS_PC_BIOS SYSBIOS on SYS.ResourceID=SYSBIOS.ResourceID
ORDER BY SYS.Netbios_Name0, SYS.Resource_Domain_OR_Workgr0
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question