March 29, 2018 at 3:15 am
Hello everyone
I will need to run this query on all my databasesDECLARE @t_spaceused TABLE
(
DatabaseName SYSNAME,
DatabaseNameLogic VARCHAR(255),
DatabaseNamePhysical VARCHAR(255),
TypeDesc VARCHAR(20)
);
DECLARE @sql VARCHAR(1000);
SET @sql = 'USE [' + '?' + '];
WITH fichiers_bases AS
(
SELECT
DB_NAME() AS DatabaseName,
name AS DatabaseNameLogic,
physical_name AS DatabaseNamePhysical,
type_desc as TypeDesc
FROM sys.database_files
--WHERE type = 1 -- Données et journaux
)
SELECT
DatabaseName,
DatabaseNameLogic,
DatabaseNamePhysical,
TypeDesc
FROM fichiers_bases;';
INSERT INTO @t_spaceused
EXEC sp_MSForEachDB @sql;
SELECT
DatabaseName
,'"+context.var_InstanceSqlReferenceDataName+"'
,DatabaseNameLogic
,DatabaseNamePhysical
,TypeDesc
FROM @t_spaceused
--WHERE DatabaseName NOT IN ('master','msdb','model','tempdb') -- Bases utilisateurs + tempdb
ORDER BY DatabaseName ASC;
the pb that the account running this script must be either sysadmin or db_owner on all databases to retrieve the information
is there a way to reduce this right and have a result
thank you all
March 29, 2018 at 3:42 am
Are you sure about that? Check out the documentation for sys.database_files to find out what permissions are needed. By the way, have you considered using sys.master_files instead?
John
March 29, 2018 at 8:20 am
joujousagem2006 1602 - Thursday, March 29, 2018 3:15 AMHello everyoneI will need to run this query on all my databases
DECLARE @t_spaceused TABLE
(
DatabaseName SYSNAME,
DatabaseNameLogic VARCHAR(255),
DatabaseNamePhysical VARCHAR(255),
TypeDesc VARCHAR(20)
);DECLARE @sql VARCHAR(1000);
SET @sql = 'USE [' + '?' + '];
WITH fichiers_bases AS
(
SELECT
DB_NAME() AS DatabaseName,
name AS DatabaseNameLogic,
physical_name AS DatabaseNamePhysical,
type_desc as TypeDesc
FROM sys.database_files
--WHERE type = 1 -- Données et journaux
)
SELECT
DatabaseName,
DatabaseNameLogic,
DatabaseNamePhysical,
TypeDesc
FROM fichiers_bases;';INSERT INTO @t_spaceused
EXEC sp_MSForEachDB @sql;SELECT
DatabaseName
,'"+context.var_InstanceSqlReferenceDataName+"'
,DatabaseNameLogic
,DatabaseNamePhysical
,TypeDesc
FROM @t_spaceused
--WHERE DatabaseName NOT IN ('master','msdb','model','tempdb') -- Bases utilisateurs + tempdb
ORDER BY DatabaseName ASC;
the pb that the account running this script must be either sysadmin or db_owner on all databases to retrieve the information
is there a way to reduce this right and have a result
thank you all
sys.database_files exists in each database and requires membership of the public role
sys.master_files exists in the master database and requires view any definition
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 29, 2018 at 11:06 am
The account running the job with sys.database_files must have CONNECT permission in each database, but sys.database_files is publicly readable and requires no additional permission. Databases that they can't connect to will be skipped.
An account with VIEW ANY DEFINITION rights on the server can use sys.master_files and will report on all databases.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply