sqldbafamily

Shared components are installed in the wrong location when you implement a SQL Server 2012 Failover Cluster installation

2014-09-17T16:15:00.000+10:00

Issue:

This has been an issue with SQL Server 2008 (http://support.microsoft.com/kb/2028830) and its still occurring in 2012.

If we have shared components installed to any drives other than default C:\ drive, this issue will occur. Also, we wont find this issue unless we fail over to the corrupt node. Then after fail over we will find that the LS is failing and errors clearly indicate that it can't find Logship.exe file.

Version we had running: SQL Server Standard Edition 2012 + SP1

Workaround:

Whenever the node is failed over to the corrupt one, we can modify the LS job to look for the components in C:\ drive

Resolution:
We raised this MS support and they had us playing with the Prod system in trying out the solution.

Finally, solution is

Uninstall all components from problem node
Install SQL Server 2012 + SP2 via Slipstream

I can confirm that this has resolved the issue for us.

Note: We tried using the new method of Slipstreaming with UPDATESOURCE and we did face some issues. Hence we went back and did it the old way (with PCUSOURCE). I am gonna post another article soon on how to slipstream.

MvM

Error while trying to open ALERTS in SQL Server

2014-07-08T11:31:00.001+10:00

Here below is the error i got while trying to open ALERTS from sql server. The version i had here is SQL 2012.

TITLE: Microsoft SQL Server Management Studio

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

Cannot show requested dialog.

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

ADDITIONAL INFORMATION:

Cannot show requested dialog. (SqlMgmt)

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

Failed to create/initialize Agent Alert dialog. (SqlManagerUI)

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

Cannot create/initialize Response page. (SqlManagerUI)

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

An item with the same key has already been added. (mscorlib)

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

BUTTONS:

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

ISSUE

Issue here is that we have Agent Jobs set up with the same name.

From the error message, we see this: An item with the same key has already been added. (mscorlib)

It means there are two jobs with the same name. In this situation, SQL Server will prevent all operations on alert.

RESOLUTION

We need to find the duplicate jobs and either rename them or delete them if not required. This should fix the issue.

MvM

Cumulative Update #12 for SQL Server 2008 SP3

2013-07-17T11:52:00.002+10:00

http://blogs.msdn.com/b/sqlreleaseservices/archive/2013/07/16/cumulative-update-12-for-sql-server-2008-sp3.aspx

The 12^th cumulative
update release for SQL Server 2008 Service Pack 3 is now available for download
at the Microsoft Support site. Cumulative Update 12 contains all the hotfixes
released since the initial release of SQL Server 2008 SP3.

MvM

Cumulative Update #5 for SQL Server 2012 SP1

2013-07-17T11:50:00.005+10:00

http://blogs.msdn.com/b/sqlreleaseservices/archive/2013/07/16/cumulative-update-5-for-sql-server-2012-sp1.aspx

The 5^th cumulative update release for SQL Server 2012 SP1 is now available for download at the Microsoft Support site.

MvM

Error : Msg 3117, Level 16, State 4 The log or differential backup cannot be restored because no files are ready to rollforward

2012-11-29T16:13:00.000+11:00

Error : Msg 3117, Level 16, State 4 The log or differential backup cannot be restored because no files are ready to rollforward

I restored a Full backup and then tried restoring a transaction log with NORECOVERY and thats how i obtained this error. And that exactly is the issue.

Inorder for trn logs to be restored, full backup restore should have been done with NORECOVERY.

Perform it that way and restores work like charm.

MvM

Cannot connect to .Login failed for user ''. Reason: Server is in script upgrade mode. Only administrator can connect at this time.

2012-11-15T15:46:00.000+11:00

TITLE: Connect to Server
------------------------------
Cannot connect to MYINSTANCE.
------------------------------
ADDITIONAL INFORMATION:
Login failed for user 'MY_LOGIN'. Reason: Server is in script upgrade mode. Only administrator can connect at this time.

Error 18401

This is something which I faced when I was trying to connect to my SQL Server instance, just after applying SQL Server 2008 R2 SP2+CU2. I was a little puzzled as this was the first time when I faced this issue.

Just to let you know, this isnt a big deal. This has already been reported as a BUG.

https://connect.microsoft.com/SQLServer/feedback/details/416860/error-18401-after-starting-service-after-successfull-sp1-install

I havent had done anything specific other than the normal run for the patch installation.

1. Apply SP2
2. Reboot
3. Apply CU2.
4. Check SSMS

ISSUE & CAUSE:
***************
SP installation completes successfully but some of the scripts (Ex: sqlagent100_msdb_upgrade.sql) in the SP will be applied only after the SQL Server service starts the next time. These scripts were getting applied to the instance and I was trying to connect to the instance during that time. This caused the error mesage to pop up.

RESOLUTION:
*************
Patience is the only solution to this, as you have to wait some time for the scripts to be applied completely and after this has been done, any one whos authorized to connect to the DB will be allowed to connect as normal.

I did the same and that worked !!!

MvM

Files Are Not Deleted From Recycler Folder

2012-10-25T10:21:00.001+11:00

I have encountered an issue today where the drive was filling up. I deleted some old backups from the drive and still there were some hidden files which were taking up space. when i checked, it was the RECYCLER folder. This was keeping some of the very old deleted files, which i had to delete manually and release space to the drive. Here below is the link to the MS site about this and how to delete the contents of this folder.

http://support.microsoft.com/kb/229041

To resolve this issue, delete the files from an MS-DOS prompt. To do this, follow these steps:

1.Click Start, click Run, type cmd.exe in the Open box, and then click OK.

2.Change to the drive and folder where you deleted the files. For example if you deleted a file from the C:\Windows folder, type cd\windows at the C: prompt, and then press ENTER.

3.From that folder type cd recycler, and then press ENTER.

4.From the Recycler folder type dir, and then press ENTER. You may see some UserSID folders where SID is the security ID for each user who deleted files in that folder.

5.Type cd userSID, and then press ENTER.

6.Type del *.*, and then press ENTER. If you receive an error message that indicates some files are open, quit all the programs running on your computer.

7.Type cd.., press ENTER, and then repeat steps 5-7 for each folder in the Recycler folder.

8.Type exit, and then press ENTER.

MvM

Cumulative update package 3 for SQL Server 2008 R2 Service Pack 2

2012-10-18T12:17:00.004+11:00

Build 10.50.4266.0 (cumulative update)

This cumulative update package was released on October 15, 2012.

The parent build of this build is 10.50.4263.0.

For more information about this build, click the following article number to view the article in the Microsoft Knowledge Base:

2754552 http://support.microsoft.com/kb/2754552

Cumulative update package 3 for SQL Server 2008 R2 Service Pack 2

SQL Server 2008 R2 SP2 CU2 fails - sqlagtctr.dll

2012-10-18T11:35:00.004+11:00

I have faced isssues with few systems now regarding the DLL 'sqlagtctr.dll'

====== From C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log ======

Final result: The patch installer has failed to update the following instance: MSSQLSERVER. To determine the reason for failure, review the log files.

MSSQL10_50.MYSERVER-sqlagtctr.dll. ---> Microsoft.SqlServer.Configuration.Sco.ScoException: The process cannot access the file 'C:\Windows\system32\perf-MSSQL10_50.MYSERVER-sqlagtctr.dll' because it is being used by another process. ---> System.IO.IOException: The process cannot access the file 'C:\Windows\system32\perf-MSSQL10_50.MYSERVER-sqlagtctr.dll' because it is being used by another process.

MSSQL10_50.MYSERVER-sqlagtctr.dll. ---> Microsoft.SqlServer.Configuration.Sco.ScoException: The process cannot access the file 'C:\Windows\SysWOW64\perf-MSSQL10_50.MYSERVER-sqlagtctr.dll' because it is being used by another process. ---> System.IO.IOException: The process cannot access the file 'C:\Windows\SysWOW64\perf-MSSQL10_50.MYSERVER-sqlagtctr.dll' because it is being used by another process.

With workarounds from Microsoft sites, I have renamed these DLL’s and have completed the upgrade successfully.

http://blogs.msdn.com/b/karthick_pk/archive/2012/07/16/the-process-cannot-access-the-file-c-windows-system32-perf-mssql10-50-mssqlserver-sqlagtctr-dll-because-it-is-being-used-by-another-process.aspx

http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/06fc5c42-9d5d-4331-a4b9-210559411eee

https://connect.microsoft.com/SQLServer/feedback/details/576412/-the-process-cannot-access-the-file-c-windows-syswow64-perf-mssql10-50-mssqlserver-sqlagtctr-dll-because-it-is-being-used-by-another-process

A similar issue to the one I mentioned above, that might occur with upgrading of cluster resources during SP2 install. (http://msdn.microsoft.com/en-US/library/hh995349(v=sql.15).aspx)

2.2 Setup Might Fail if SQAGTRES.dll Is Locked by Another Process

Issue: A SQL Server setup operation might fail with this error: Upgrading of cluster resource C:\Program Files\Microsoft SQL Server\MSSQL10_50.\MSSQL\Binn\SQAGTRES.DLL on machine failed with Win32Exception. Please look at inner exception for details. The root cause is that C:\Windows\system32\SQAGTRES.DLL is locked by another process and Setup was not able to update it.

Workaround: Rename C:\Windows\system32\SQAGTRES.DLL to a temporary name such as C:\Windows\system32\SQAGTRES_old.DLL, and then select the Retry option on the setup error message. That will allow Setup to continue. After a reboot, you can delete the temporary file C:\Windows\system32\SQAGTRES_old.DLL.

MvM

Msg 7311, Level 16, State 2, Line 1 Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI10" for linked server " ". The provider supports the interface, but returns a failure code when it is used.

2012-08-09T13:41:00.000+10:00

I was trying to run a job from a SQL Server 2008 R2 64bit Test machine which connects to a 32bit SQL Server 2000 Production machine through a Linked Server. When I tried running the query after creating the Linked Server, it throwed up the following error message.

OLE DB provider "SQLNCLI10" for linked server "FOCUS" returned message "Unspecified error".

OLE DB provider "SQLNCLI10" for linked server "FOCUS" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".

Msg 7311, Level 16, State 2, Line 1

Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI10" for linked server "FOCUS". The provider supports the interface, but returns a failure code when it is used.

Since I had ran this query directly on PROD, I was pretty sure that the query works fine. Then I found the link from MS site while googling.

http://support.microsoft.com/kb/906954

Then I found that it basically needs a stored procedure (sp_tables_info_rowset_64) to fix the issue between 32bit and 64bit. The complete procedure is available in multiple sites. But the fix only requires few lines of it. The code is listed below.

create procedure sp_tables_info_rowset_64

@table_name sysname,

@table_schema sysname = null,

@table_type nvarchar(255) = null

declare @Result int set @Result = 0

exec @Result =sp_tables_info_rowset @table_name, @table_schema, @table_type

MvM

Restore nightly backups script

2012-08-09T11:14:00.001+10:00

I am used to getting requests from users to restore Test databases with that of PROD. This could be ad-hoc or on schedule. The below script takes inputs as DB Name, Prod Backup location and backupfile name and restores the Test db. I have made modifications to suit my requirement.

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

-- Restore database from a backup file

-- NOTE: If the database gets locked in Single user mode, execute

-- the below command on it and it will restore the Multiuser mode:

-- ALTER DATABASE yourdatabasename SET MULTI_USER

-- Modify the @databaseName, @restoreDirectory & @backupFileName as per your requirement.

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

USE master

-- Declare the variables

DECLARE @backupFileName varchar(100),

@restoreDirectory varchar(100),

@databaseDataFilename varchar(100),

@databaseLogFilename varchar(100),

@databaseDataFile varchar(100),

@databaseLogFile varchar(100),

@databaseName varchar(100),

@execSql nvarchar(1000)

-- Set the name of the database to restore

SET @databaseName = 'YOUR_DATABASE_NAME'

-- Set the path to the directory containing the database backup file.

-- IMPORTANT:: Make sure there is a \ character at the end of your directory path as below;

SET @restoreDirectory ='LOCATION OF BACKUPFILE' -- such as '\\NETWORK\BKUP\DB\'

-- Create the backup file name based on the restore directory, the database name and today's date & time.

-- The formatting has been done as per my backup file requirement.

SET @backupFileName = @restoreDirectory + @databaseName +'_backup_'+convert(varchar,getdate(), 112)+'.bak'

PRINT @backupFileName

-- Get the data file and its path

SELECT @databaseDataFile =RTRIM([Name]),

@databaseDataFilename =RTRIM([Filename])

FROM master.dbo.sysaltfiles AS files

INNER JOIN master.dbo.sysfilegroups AS groups

ON files.groupID = groups.groupID

WHERE dbid =

(

SELECT dbid

FROM master.dbo.sysdatabases

WHERE [Name] = @databaseName

)

-- Get the log file and its path

SELECT @databaseLogFile = rtrim([Name]),

@databaseLogFilename = rtrim([Filename])

FROM master.dbo.sysaltfiles AS files

WHERE dbid =

(

SELECT dbid

FROM master.dbo.sysdatabases

WHERE [Name] = @databaseName

)

AND

groupID = 0

PRINT 'Killing active connections to the "'+ @databaseName + '" database'

-- Create the sql to kill the active database connections

SET @execSql = ' '

SELECT @execSql = @execSql +'kill '+convert(char(10), spid)+ ' '

FROM master.dbo.sysprocesses

WHERE db_name(dbid)= @databaseName

AND dbid<> 0

AND spid <> @@spid

-- EXEC (@execSql)

PRINT 'Restoring "'+ @databaseName +'" database from "'+ @backupFileName + '" with '

PRINT ' DATA file "'+ @databaseDataFile +'" located at "'+ @databaseDataFilename +'"'

PRINT ' LOG file "'+ @databaseLogFile +'" located at "'+ @databaseLogFilename + '"'

SET @execSql = 'restore database [' + @databaseName + ']

from disk = ''' + @backupFileName + '''

with

file = 1,

move ''' + @databaseDataFile +''' to '+''''+ @databaseDataFilename + ''',

move ''' + @databaseLogFile +''' to '+''''+ @databaseLogFilename + ''',

norewind,

nounload,

replace'

PRINT @execsql

EXEC sp_executesql @execSql

---------------End of Restore script---------------------------------------

MvM

Biggest Tables in a database - SQL Server 2000

2012-08-02T21:37:00.001+10:00

The below mentioned script is only for SQL Server 2000 and I already have posted a simpler script to find the same from SQL 2005/2008.

declare @id int

declare @type character(2)

declare @pages int

declare @dbname sysname

declare @dbsize dec(15,0)

declare @bytesperpage dec(15,0)

declare @pagesperMB dec(15,0)

create table #spt_space

( objid int null, rows int null, reserved dec(15) null,

data dec(15) null, indexp dec(15) null, unused dec(15) null)

set nocount on

-- Create a cursor to loop through the user tables

declare c_tables cursor for

select id

from sysobjects

where xtype = 'U'

open c_tables

fetch next from c_tables into @id

while @@fetch_status= 0

begin

/* Code from sp_spaceused */

insert into #spt_space(objid, reserved)

select objid= @id, sum(reserved)

from sysindexes

where indid in (0, 1, 255) and id = @id

select @pages =sum(dpages)

from sysindexes where indid < 2and id = @id

select @pages = @pages +isnull(sum(used), 0)

from sysindexes where indid = 255 and id = @id

update #spt_space

set data = @pages

where objid= @id

/* index: sum(used) where indid in (0, 1, 255) - data */

update #spt_space

set indexp =(selectsum(used)

from sysindexes where indid in(0, 1, 255) and id = @id)- data

where objid= @id

/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */

update #spt_space

set unused = reserved-(selectsum(used)

from sysindexes where indid in(0, 1, 255) and id = @id)

where objid= @id

update #spt_space

set rows= i.rows

from sysindexes i

where i.indid < 2 and i.id = @id and objid= @id

fetch next from c_tables into @id

end

select top 25

Table_Name = (select left (name,25) from sysobjects where id =objid),

rows = convert(char(11), rows),

reserved_KB = ltrim(str(reserved * d.low / 1024.,15,0)+' '+'KB'),

data_KB = ltrim(str(data * d.low / 1024.,15,0)+' '+'KB'),

index_size_KB = ltrim(str(indexp * d.low / 1024.,15,0)+' '+'KB'),

unused_KB = ltrim(str(unused * d.low / 1024.,15,0)+' '+'KB')

from #spt_space, master.dbo.spt_values d

where d.number = 1

and d.type= 'E'

order by reserved desc

drop table #spt_space

close c_tables

deallocate c_tables

MvM

Database Restore History

2012-08-02T21:36:00.000+10:00

USE msdb;

Select top 20

DBRestored= destination_database_name,

SourceFile= physical_name,

RestoreDate= restore_date

from RestoreHistory h

inner join BackupSet b

on h.backup_set_id = b.backup_set_id

inner join BackupFile f

on f.backup_set_id = b.backup_set_id

order by RestoreDate desc

MvM

List Indexes and their size

2012-07-23T13:24:00.001+10:00

SELECT OBJECT_NAME(i.OBJECT_ID)AS TableName,

i.name AS IndexName, i.index_id AS IndexID,

(8 *SUM(a.used_pages))/1024 AS 'Indexsize(MB)'

FROM sys.indexes AS i

JOIN sys.partitions AS p ON p.OBJECT_ID= i.OBJECT_ID AND p.index_id = i.index_id

JOIN sys.allocation_units AS a ON a.container_id = p.partition_id

GROUP BY i.OBJECT_ID, i.index_id,i.name

ORDER BY OBJECT_NAME(i.OBJECT_ID), i.index_id

MvM

List all indexes & their included columns in a database

2012-07-23T13:22:00.001+10:00

SELECT '['+Sch.name+'].['+ Tab.[name]+']' AS TableName,

Ind.[name] AS IndexName,

SUBSTRING ((SELECT', '+ AC.name

FROM sys.[tables] AS T

INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]

INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]

AND I.[index_id] = IC.[index_id]

INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]

AND IC.[column_id] = AC.[column_id]

WHERE Ind.[object_id] = I.[object_id]

AND Ind.index_id = I.index_id AND IC.is_included_column = 0

ORDER BY IC.key_ordinal

FOR XML PATH('')), 2, 8000) AS KeyCols,

SUBSTRING ((SELECT', '+ AC.name

FROM sys.[tables] AS T

INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]

INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]

AND I.[index_id] = IC.[index_id]

INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]

AND IC.[column_id] = AC.[column_id]

WHERE Ind.[object_id] = I.[object_id] AND Ind.index_id = I.index_id

AND IC.is_included_column = 1

ORDER BY IC.key_ordinal

FOR XML PATH('')), 2, 8000) AS IncludeCols

FROM sys.[indexes] Ind

INNER JOIN sys.[tables] AS Tab ON Tab.[object_id] = Ind.[object_id]

INNER JOIN sys.[schemas] AS Sch ON Sch.[schema_id] = Tab.[schema_id]

ORDER BY TableName

MvM

List larger tables in a database

2012-07-23T13:21:00.000+10:00

I have tried this query only on sql 2008. This will extract all tables with number of rows, reserved,data,index and unused space in MB.

select

name =object_schema_name(object_id)+'.'+object_name(object_id),

rows =sum(case when index_id < 2 then row_count else 0 end),

reserved_MB = 8*sum(reserved_page_count)/1024,

data_MB = 8*sum(case when index_id<2

then in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count

else lob_used_page_count + row_overflow_used_page_count end)/1024,

index_MB = 8*(sum(used_page_count)-sum(case when index_id<2

then in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count

else lob_used_page_count + row_overflow_used_page_count end))/1024,

unused_kb = 8*sum(reserved_page_count-used_page_count)

from sys.dm_db_partition_stats

where object_id> 1024

group by object_id

order by rows desc;

MvM

List Recovery Model for all databases in SQL Server 2000

2012-07-23T13:19:00.004+10:00

Two ways I used to find it. But the best one is the first one listed.

SELECT Name,

DATABASEPROPERTYEX (Name,'RECOVERY') AS [Recovery Model]

FROM master.dbo.sysdatabases

sp_msforeachdb

'use [?] select ''?'' as DatabaseName, DATABASEPROPERTYEX(N''?'', N''RECOVERY'')'

MvM

Query to find Database Details in SQL Server 2000

2012-07-23T13:19:00.003+10:00

Here's the SQL Server 2000 version of the query.
Add columns as per your requirement.

select Name, SUSER_SNAME(owner_sid) as Owner,

[Total Size MB], cmptlevel

from sysdatabases MST inner join (select b.name [LOG_DBNAME],

CONVERT(DECIMAL(10,2), sum(CONVERT(DECIMAL(10,2),(a.size * 8))/1024))

as [Total Size MB] from sysaltfiles A

inner join sysdatabases B on A.dbid= B.dbid

group by b.name)AA on AA.[LOG_DBNAME] = MST.name

order by owner;

MvM

2012-07-23T13:19:00.001+10:00

Here's the SQL Server 2000 version of the query.
Add columns as per your requirement.

select Name, SUSER_SNAME(owner_sid) as Owner,

[Total Size MB], cmptlevel

from sysdatabases MST inner join (select b.name [LOG_DBNAME],

CONVERT(DECIMAL(10,2), sum(CONVERT(DECIMAL(10,2),(a.size * 8))/1024))

as [Total Size MB] from sysaltfiles A

inner join sysdatabases B on A.dbid= B.dbid

group by b.name)AA on AA.[LOG_DBNAME] = MST.name

order by owner;

MvM

Query to find Database Details in SQL Server 2005 & 2008

2012-07-23T13:14:00.001+10:00

Here's the query which I am using.

select Name, SUSER_SNAME(owner_sid) as Owner, create_date,

state_desc, [Total Size MB], recovery_model_desc,

compatibility_level, collation_name, user_access_desc

from sys.databases MST

inner join (select b.name [LOG_DBNAME],

CONVERT(DECIMAL(10,2), sum(CONVERT(DECIMAL(10,2), (a.size * 8))/1024)) [Total Size MB]

from sys.sysaltfiles A

inner join sys.databases B on A.dbid= B.database_id

group by b.name) AA on AA.[LOG_DBNAME] = MST.name

order by owner

MvM

Ctrl Alt Del in Remote Desktop

2012-07-23T13:11:00.000+10:00

I have had this situation so many times that I wanted to press ctrl+alt+del in my remote desktop window and I did and then the local computer responds.
Then, how to do it? Here’s the wonder…Its…

CTRL + ALT + END

wow…that sounds bad…but yes, thats the way out…

MvM

[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’.

2012-07-23T13:08:00.002+10:00

I have seen this error lot of times in SQL Server 2000 Maintenance Plans. When job fails, it doesn’t specifically say anything in the ‘Job History’. So, what I used to do is to run the Optimisation code (copied from the job) in Query Analyzer. This will list out each step result for the plan. Thats how I used to see this error message. This specifically appears for the ‘Integrity Checks’ and ‘Optimisation’ Job Plans.

Here’s how to resolve it.

Optimizations Job for DB Maintenance Plan ‘DB Maintenance Plan name‘For example, add the parameters to the command as shown in the following command:
EXECUTE master.dbo.xp_sqlmaint N’-S ServerName\InstanceName -PlanID -WriteHistory -RebldIdx 10 -SupportComputedColumn’

Integrity Checks Job for DB Maintenance Plan ‘DB Maintenance Plan name‘For example, add the parameters to the command as shown in the following command:
EXECUTE master.dbo.xp_sqlmaint N’-S ServerName\InstanceName -PlanID -WriteHistory -CkDB -SupportComputedColumn’

More details and explanations can be found in http://support.microsoft.com/kb/902388

MvM

Change Maintenance Plan Owner to ‘sa’

2012-07-12T22:18:00.002+10:00

I always had this annoying issue where in I had to update the job owner to ‘sa’ once someone had modified a maintenance plan. This happens when a user logs in with his id (which usually everyone does) and modifies a Maintenance Plan and then saves it. This automatically changes the owner of the job corresponding to it.

Below mentioned query is handy when you need to update lot of Maintenance Plans like this.

–SQL 2005

UPDATE msdb.dbo.sysdtspackages90

SET ownersid =SUSER_SID(‘sa’)

WHERE name = ‘MAINT’

–SQL 2008

UPDATE msdb.dbo.sysssispackages

SET ownersid =SUSER_SID(‘sa’)

WHERE name = ‘MAINT’

–**************************

MvM

Database, Users and their DB Roles (SQL Server 2000 & 2005)

2012-07-12T22:16:00.001+10:00

SQL SERVER 2005

DECLARE @db_name SYSNAME, @sql VARCHAR(1000)

DECLARE db_cursor CURSOR FOR

SELECT Name FROM sys.databases

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @db_name

WHILE (@@FETCH_STATUS= 0)

BEGIN

SET @sql =‘SELECT ”’+ @db_name + ”’ AS [Database],

USER_NAME(role_principal_id) AS [Role], USER_NAME(member_principal_id) AS [User]

FROM ‘+ @db_name +‘.sys.database_role_members’

EXEC(@sql)

FETCH NEXT FROM db_cursor INTO @db_name

END

CLOSE db_cursor

DEALLOCATE db_cursor

SQL SERVER 2000

DECLARE @db_name SYSNAME, @sql VARCHAR(1000)

DECLARE db_cursor CURSOR FOR

SELECT Name FROM master..sysdatabases

OPEN db_cursor FETCH NEXT

FROM db_cursor INTO @db_name

WHILE (@@FETCH_STATUS= 0)

BEGIN

SET @sql =‘SELECT ”’+ @db_name + ”’ AS [Database],

USER_NAME(memberuid) AS [Role], USER_NAME(groupuid) AS [User]

FROM sysmembers’

EXEC (@sql)

FETCH NEXT FROM db_cursor INTO @db_name

END

CLOSE db_cursor

DEALLOCATE db_cursor;

–====================================

MvM

Query to find Users and their Database Roles in SQL Server 2000

2012-07-12T22:11:00.001+10:00

SELECT USER_NAME(memberuid) as UserName,

USER_NAME(groupuid) as Role

FROM sysmembers ;

MvM