Script to shrink all DB's with in SQL server 2005.

SET NOCOUNT ON

DECLARE @DBName varchar(100)
DECLARE @FileName varchar(100)
DECLARE @TempTxt varchar(1000)
DECLARE @Cnt smallint

SET @TempTxt = 'USE Master;' + CHAR(13) +
'EXEC xp_fixeddrives;'

DECLARE cmdtxt_cursor CURSOR FOR SELECT  RTRIM(master..Sysdatabases.Name) as [DBName],
         RTRIM(master..Sysaltfiles.Name) as [FileName]
FROM  master..Sysdatabases
Inner join master..Sysaltfiles on master..Sysaltfiles.DBID = master..Sysdatabases.DBID
WHERE   master..Sysdatabases.DBID not in (1,2,3,4) and Patindex('%log%', master..Sysaltfiles.Name) > 0
and ((master..Sysaltfiles.Size)*8/1024) > 0 and Patindex ('% %', master..Sysdatabases.Name) = 0
ORDER BY Left(master..Sysaltfiles.FileName, 1)

SET @TempTxt = 'USE Master;' + CHAR(13) +
'EXEC xp_fixeddrives;'
--PRINT @TempTxt
EXEC (@TempTxt)

OPEN cmdtxt_cursor
FETCH NEXT FROM cmdtxt_cursor INTO @DBName, @FileName
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT 'Processing : ' + @DBName
If Exists(Select master..Sysdatabases.[Name] From master..Sysdatabases
           Where master..Sysdatabases.[Name] = @DBName)
BEGIN
SET @TempTxt = 'USE [' + @DBName + '];' + CHAR(13) +
'BACKUP LOG [' + @DBName + '] WITH TRUNCATE_ONLY ;' + CHAR(13) +
'DBCC SHRINKFILE (''' + @FileName + ''', 1);'
--PRINT @TempTxt
EXEC (@TempTxt)
FETCH NEXT FROM cmdtxt_cursor INTO @DBName, @FileName
END
END

CLOSE cmdtxt_cursor
DEALLOCATE cmdtxt_cursor
SET @TempTxt = 'USE Master;' + CHAR(13) +
'EXEC xp_fixeddrives;'
--PRINT @TempTxt
EXEC (@TempTxt)
GO

Error Code 1326 - Not able to execute xp_cmdshell

Issue :-  A domain SQL service account password has been changes and after that the user is complaining that he is not able to access xp_cmdshell.

Error 1326 was seen.

Resolution :- We checked the SQL server properties and found that the same account was being used as a proxy account in SQL server. Updated the new password of the service account at proxy account and then it was able to access the xp_cmdshell.

Script to grant database level role to a login on all user database

Below script will grant db_owner role to a newly created login on all user databases. We can use the same script to grant any database level role to the login on all user database.

Script 1 :- For Windows Login

create login loginname from windows

DECLARE @command varchar(1000)
SELECT @command = 'IF ''?'' NOT IN(''master'', ''msdb'', ''tempdb'',''model'')
BEGIN USE ? EXEC(''create user username for login loginname'') END '

EXEC sp_MSforeachdb @command

DECLARE @command1 varchar(1000)

Select @command1 = 'IF ''?'' NOT IN(''master'', ''msdb'', ''tempdb'',''model'')
BEGIN USE ? EXEC sp_addrolemember ''db_owner'', ''username'' END'

EXEC sp_MSforeachdb @command1


For SQL Login

create login loginname with password = ' Enter the password for the login'

DECLARE @command varchar(1000)
SELECT @command = 'IF ''?'' NOT IN(''master'', ''msdb'', ''tempdb'',''model'')
BEGIN USE ? EXEC(''create user username for login loginname'') END '

EXEC sp_MSforeachdb @command

DECLARE @command1 varchar(1000)

Select @command1 = 'IF ''?'' NOT IN(''master'', ''msdb'', ''tempdb'',''model'')
BEGIN USE ? EXEC sp_addrolemember ''db_owner'', ''username'' END'

EXEC sp_MSforeachdb @command1