Technical Article

Instance wide script to add user (the 4th way)

,

There are times as a DBA/Developer when you want to run a SQL query against all databases hosted on an instance. A lot was written about different ways of performing this task. Different people got different preference of the methods used to achieve their goals.  I have used three of the methods (while loop, sp_MSforeachdb and cursor). I wouldn’t say one is better than the other apart from sp_MSforeachdb which is undocumented stored procedure and can be obsolete as and when Microsoft decides to drop it. I want the community aware of the 4th ways of performing the task.

1. to use the script as it is replace the mydomain\teshome with your own logon and/or AD group or SQL account.
2.  To change the script to perform something else then you need to replace entire code in 
"IF NOT EXISTS (SELECT * from sys.sysusers  WHERE name = N''mydomain\teshome'')
BEGIN
CREATE USER [mydomain\teshome] FOR LOGIN [mydomain\teshome];
ALTER USER [mydomain\teshome] WITH DEFAULT_SCHEMA=[dbo];
ALTER ROLE [db_datareader] ADD MEMBER [mydomain\teshome];
END"
for instance if you want to query size of all databases you can replace the above script with the following and it should work.
SELECT db_name() as DBName, filegroup_name(groupid) FileGroupName,a.name, Filename,
CONVERT(Decimal(15,2),ROUND(a.Size/128.000,2)) [AllocatedInMB],
CONVERT(Decimal(15,2),ROUND(FILEPROPERTY(a.Name,''SpaceUsed'')/128.000,2)) AS [UsedInMB],
CONVERT(Decimal(15,2),ROUND((a.Size-FILEPROPERTY(a.Name,''SpaceUsed''))/128.000,2)) AS [AvailInMB],
MaxSize = CASE  WHEN   a.growth = 0 THEN cast(CONVERT(Decimal(15,2),ROUND(a.Size/128.000,2)) AS VARCHAR) + '' MB''
WHEN a.maxsize = -1 then ''Space/File Max'' ELSE cast (cast (a.maxsize*1.0/128 as decimal(9,2)) as nvarchar(30)) + '' MB'' end, 
[%SpaceUsed] = CASE  WHEN a.growth = 0 
THEN  CONVERT(varchar,cast(ROUND(FILEPROPERTY(a.Name,''SpaceUsed'')/128.000,2)/ROUND(a.Size/128.000,2)*100 AS  decimal(9,2)) )
WHEN  a.maxsize = -1 AND a.growth > 0 THEN ''N/A''
WHEN a.maxsize = 0 THEN ''Review'' ELSE cast (cast(((a.size*1.0)/(a.maxsize*1.0))*100 as decimal(9,2)) as nvarchar(30)) End,
GrowthBy = case when sm.is_percent_growth = 1 then cast(a.growth as varchar)+ ''%''
when a.growth = 0 then ''Not Allowed'' else cast (cast (a.growth*1.0/128 as decimal(9,2)) as nvarchar(30)) + '' MB'' end
FROM dbo.sysfiles a  join sys.master_files sm on a.fileid = sm.file_id where sm.database_id = DB_ID()'
declare @SQL varchar(max)
set @SQL = ''
select @SQL = @SQL + CHAR(13) + 'USE ' + QUOTENAME([name]) + ';
IF NOT EXISTS (SELECT * from sys.sysusers  WHERE name = N''mydomain\teshome'')
BEGIN
CREATE USER [mydomain\teshome] FOR LOGIN [mydomain\teshome];
ALTER USER [mydomain\teshome] WITH DEFAULT_SCHEMA=[dbo];
ALTER ROLE [db_datareader] ADD MEMBER [mydomain\teshome];
END'
from sys.databases  
WHERE database_id > 4 
execute (@SQL)    
-- select @SQL

Rate

4 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

4 (2)

You rated this post out of 5. Change rating