It is always a risk if the user name & password is equal. You can run the below script to find out the list of User/Login name & password which are equal.
select cast(@@SERVERNAME as varchar(150)) as SQLInstanceName ,name as [LoginName] ,'Password is same as Login Name' [Description] from sys.syslogins WHERE PWDCOMPARE (name,password) = 1
It is always good to have the idea about the database properties while doing the migration, performance tuning or configuration.
You can execute the below script to get the database property information.
select sysDB.database_id, sysDB.Name as 'Database Name', syslogin.Name as 'DB Owner', sysDB.state_desc, sysDB.recovery_model_desc, sysDB.collation_name, sysDB.user_access_desc, sysDB.compatibility_level, sysDB.is_read_only, sysDB.is_auto_close_on, sysDB.is_auto_shrink_on, sysDB.is_auto_create_stats_on, sysDB.is_auto_update_stats_on, sysDB.is_fulltext_enabled, sysDB.is_trustworthy_on from sys.databases sysDB INNER JOIN sys.syslogins syslogin ON sysDB.owner_sid = syslogin.sid
sys.dm_os_ring_buffers: You can use the undocumented Ring Buffer DMV to troubleshoot the below issues.
Execute the below query to get the distinct ring buffer type.
select distinct ring_buffer_type from sys.dm_os_ring_buffers
Check below script as example to troubleshoot the Security Issue using ring buffer. You can change the ring buffer type in below script to troubleshoot the different issues.
-- Check the Ring Buffer in SQL Server 2008
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_WARNINGS ON
SET ANSI_PADDING ON
SELECT CONVERT (varchar(30), GETDATE(), 121) as Run_Time,
dateadd (ms, (ST.[RecordTime] - sys.ms_ticks), GETDATE()) as [Notification_Time],
ST.* , sys.ms_ticks AS [Current Time]
FROM
(SELECT
RBXML.value('(//Record/Error/ErrorCode)[1]', 'varchar(30)') AS [ErrorCode],
RBXML.value('(//Record/Error/CallingAPIName)[1]', 'varchar(255)') AS [CallingAPIName],
RBXML.value('(//Record/Error/APIName)[1]', 'varchar(255)') AS [APIName],
RBXML.value('(//Record/Error/SPID)[1]', 'int') AS [SPID],
RBXML.value('(//Record/@id)[1]', 'bigint') AS [Record Id],
RBXML.value('(//Record/@type)[1]', 'varchar(30)') AS [Type],
RBXML.value('(//Record/@time)[1]', 'bigint') AS [RecordTime]
FROM (SELECT CAST (record as xml) FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_SECURITY_ERROR') AS RB(RBXML)) ST
CROSS JOIN sys.dm_os_sys_info sys
ORDER BY ST.[RecordTime] ASC
-- Script to Check the Ring Buffer in SQL Server 2005
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_WARNINGS ON
SET ANSI_PADDING ON
SELECT CONVERT (varchar(30), GETDATE(), 121) as runtime,
DATEADD (ms, -1 * ((sys.cpu_ticks / sys.cpu_ticks_in_ms) - ST.[RecordTime]), GETDATE()) AS NotificationTime,
ST.* , sys.ms_ticks AS [CurrentTime]
FROM
(SELECT
RBXML.value('(//Record/Error/ErrorCode)[1]', 'varchar(30)') AS [ErrorCode],
RBXML.value('(//Record/Error/CallingAPIName)[1]', 'varchar(255)') AS [CallingAPIName],
RBXML.value('(//Record/Error/APIName)[1]', 'varchar(255)') AS [APIName],
RBXML.value('(//Record/Error/SPID)[1]', 'int') AS [SPID],
RBXML.value('(//Record/@id)[1]', 'bigint') AS [Record Id],
RBXML.value('(//Record/@type)[1]', 'varchar(30)') AS [Type],
RBXML.value('(//Record/@time)[1]', 'bigint') AS [RecordTime]
FROM (SELECT CAST (record as xml) FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_SECURITY_ERROR') AS RB(RBXML)) ST
CROSS JOIN sys.dm_os_sys_info sys
ORDER BY ST.[RecordTime] ASC
From the output we can see the hexadecimal error code 0x6FD. You have to convert these error code into decimal value, which will be 0x6FD = 1789
Check the above decimal error codes using the NET HELPMSG command, which will give you more information on the issue.
You can execute the below script to get the SQL Server Properties.
create table #server(ID int, Name sysname null, Internal_Value int null, Value nvarchar(512) null)
insert #server exec master.dbo.xp_msver
declare @RegRootDir nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @RegRootDir OUTPUT
SELECT
(select Value from #server where Name = N'ProductName') AS [Product],
SERVERPROPERTY(N'ProductVersion') AS [VersionString],
(select Value from #server where Name = N'Language') AS [Language],
(select Value from #server where Name = N'Platform') AS [Platform],
CAST(SERVERPROPERTY(N'Edition') AS sysname) AS [Edition],
(select Internal_Value from #server where Name = N'ProcessorCount') AS [Processors],
(select Value from #server where Name = N'WindowsVersion') AS [OSVersion],
(select Internal_Value from #server where Name = N'PhysicalMemory') AS [PhysicalMemory],
CAST(SERVERPROPERTY('IsClustered') AS bit) AS [IsClustered],
@RegRootDir AS [RootDirectory],
convert(sysname, serverproperty(N'collation')) AS [Collation]
drop table #server
Output
| Product | VersionString | Language | Platform | Edition | Processors | OSVersion | PhysicalMemory | IsClustered | RootDirectory | Collation |
| Microsoft SQL Server | 10.0.1600.22 | English (United States) | NT INTEL X86 | Enterprise Edition | 4 | 6.0 (6002) | 3496 | 0 | C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL | SQL_Latin1_General_CP1_CI_AS |
sys.sql_logins: Returns one row for each SQL Server authentication login. It will return all the below columns.
You can query sys.sql_logins to get all the below information.
SQL Logins which are disabled:
SELECT name FROM [sys].[sql_logins] WHERE [is_disabled] = 1;
SQL Server Logins which adhere the password policy:
SELECT name FROM [sys].[sql_logins] WHERE [is_policy_checked] = 1;
SQL Server Logins which do not adhere to the password policy
SELECT name FROM [sys].[sql_logins] WHERE [is_policy_checked] = 0;
SQL Logins which do not adhere to password expiration
SELECT name FROM [sys].[sql_logins] WHERE [is_policy_checked] = 0 OR ([is_policy_checked] = 1 AND [is_expiration_checked] = 0);
aiDeeva.com 