Whether or not photography is allowed, and what kind of equipment is allowed, varies not only by museum or installation, but by collection or exhibit. Where it is allowed or disallowed, the museum is unlikely to draw a distinction between a point-and-shoot and a DSLR; after all, a DSLR from 10 or 12 years ago is probably not the equal of a modern smartphone camera. It is the act of taking a photo, and not the equipment you use to take it, that has legal or physical implications. Of the seven museum policies I have quoted below, only one even mentions camera size.
Photography may be barred, particularly in art museums, and particularly at special exhibitions, due to legal and creative concerns. Flash photography may also be barred anywhere it could be damaging to artifacts or to patrons' experience. Increasingly, however, the larger museums do permit photographs to be taken, provided they are for noncommercial purposes, and taken with a handheld camera. Mounts are commonly barred due to safety and liability concerns, and probably also because they would enable you to take professional-quality images.
Typical is the National Gallery of Art in Washington's policy:
Photography for personal use is permitted except in special exhibitions and where specifically prohibited. Monopods, tripods, and selfie sticks are not permitted in the galleries or auditoriums.
Similarly, the National Portrait Gallery, part of the Smithsonian Institution, states
Photography is allowed at the National Portrait Gallery unless otherwise noted. Hand-held photos with a flash can be taken in the museum’s galleries and the Great Hall. Please be mindful that the spaces are open to the public, therefore visitors should not stage any photographs that impede others’ enjoyment of the spaces. The only place where visitors may use tripods is in the Kogod Courtyard. Standing and hand-held lights are not permitted in the building.
The Guggenheim in New York too offers that
Still photography for non-commercial, personal use is permitted, unless otherwise noted in the galleries. The use of tripods and camera extension poles is prohibited.
But the Metropolitan Museum of Art varies a bit:
Still photography is permitted for private, noncommercial use only in the Museum's galleries devoted to the permanent collection. Photographs cannot be published, sold, reproduced, transferred, distributed, or otherwise commercially exploited in any manner whatsoever. Photography is not permitted in special exhibitions or areas designated as "No Photography"; works of art on loan from private collections or other institutions may not be photographed. The use of flash is prohibited at all times and in all galleries. Movie and video cameras are prohibited. Tripods are allowed Monday through Friday, and only with a permit issued by the Information Desk in the Great Hall.
For some other famous art museums, I found the Los Angeles County Museum of Art (LACMA):
You are welcome to take personal photography and video in most of LACMA’s galleries. Exceptions are noted with this icon near the title of each work, or at the entry to select temporary exhibitions. Please, no flash or tripods.
Art Institute of Chicago:
Photography and video are permitted in the museum under the following guidelines:
- Museum staff reserves the right to prohibit the use of camera or video if it is a disruption and/or perceived that it might endanger works of art or museum patrons.
- Equipment must not exceed the size of a SLR camera designed for personal use.
- On occasion, lender restrictions prohibit photography and video in special exhibitions. We will clearly note when those restrictions apply. Otherwise photography and video are encouraged. Dry sketching is also permitted.
- Flash, tripods, monopods, handheld microphones, selfie sticks, and other extraneous equipment are not allowed in the galleries.
- All imaging documentation must be for personal, nondistributional, noncommercial use only.
San Francisco Museum of Modern Art (SF MOMA)
Photography is allowed for personal, noncommercial use (except where indicated). No flashes, tripods, selfie sticks, or videography.
Smaller museums, which may not have the manpower to patrol their patrons, may naturally be more restrictive. But as you can see, carrying a handheld camera, even a DSLR, will raise no eyebrows in the larger museums.