List orphan database users

Some days ago I was asked - politely - to move some databases between two SQL Server installations. Unfortunately the service had failed on the source, so I have no access to the logins definitions.
This give me a lot of orphan database users.
To make thing more entertaining some of the databases has compability level 80, and this can not be changed.
To get a list of orphan database users, I have to combine two methods. This is because the compability level 80 databases does not have the system view database_principals.

DECLARE @db_name AS nvarchar(128); -- Database name
DECLARE @_msg AS nvarchar(2047); -- Execution message
DECLARE @_cmd AS nvarchar(MAX); -- Dynamic command
CREATE TABLE ##orphan_user (
[db_name] nvarchar(128) NOT NULL,
[user_name] nvarchar(128) NOT NULL
);

DECLARE db_cursor CURSOR FAST_FORWARD FOR
SELECT [name]
FROM [master].[sys].[databases]
WHERE [database_id] > 4;

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @db_name;
WHILE @@FETCH_STATUS = 0
BEGIN
IF (SELECT [compatibility_level] FROM [master].[sys].[databases] WHERE [name] = @db_name) < 90 BEGIN SET @_msg = N'The database [' + @db_name + N'] is SQL Server 2000 or older.'; SET @_cmd = 'INSERT INTO ##orphan_user ' + N'SELECT ''' + @db_name + N''' AS [db_name], [sysusers].[name] AS [user_name] ' + N'FROM [' + @db_name + N'].[dbo].[sysusers] ' + N'LEFT OUTER JOIN [master].[dbo].[syslogins] ON [sysusers].[sid] = [master].[dbo].[syslogins].[sid] ' + N'WHERE [sysusers].[uid] > 4 AND [sysusers].[issqlrole]=0 AND [sysusers].[isapprole]=0 AND [master].[dbo].[syslogins].[sid] IS NULL;';
END
ELSE
BEGIN
SET @_msg = N'The database [' + @db_name + N'] is SQL Server 2005 or newer.';
SET @_cmd = N'INSERT INTO ##orphan_user ' +
N'SELECT ''' + @db_name + N''' AS [db_name], [database_principals].[name] AS [user_name] ' +
N'FROM [' + @db_name + N'].[sys].[database_principals] ' +
N'WHERE [database_principals].[type] IN (''S'',''U'',''G'') ' +
N'AND [database_principals].[is_fixed_role] = 0 ' +
N'AND [database_principals].[name] NOT IN (N''dbo'', N''guest'', N''sys'', N''INFORMATION_SCHEMA'');';
END
RAISERROR(@_msg,0,0) WITH NOWAIT;
EXECUTE (@_cmd);

FETCH NEXT FROM db_cursor INTO @db_name;
END

CLOSE db_cursor;
DEALLOCATE db_cursor;


SELECT * FROM [##orphan_user];

SELECT DISTINCT [user_name] FROM [##orphan_user]
ORDER BY [user_name] DESC

DROP TABLE [##orphan_user];

I execute only a part of the script where I fill the temporary table. Then I can filter and sort the data to handle the logins.

DBA Career Levels

Starting from the first day the career levels could be something like this.

Trainee

Assist on tasks and issues. Can be given trivial tasks with some training. Mentor assigned to introduce to profession.

Junior (Level I)

The Junior DBA resolves issues while developing professional skills. Handle assigned tasks. Check test results. Will mostly work individual, but will have a close colleage as mentor (Sensei).

Usually with up to two years of experience. But the period length is not defining - it is the capabilities.

Specialist (Level II)

Works on troubleshooting, review and test. Does now have solid fundamental skills.

Between two to five years of experience. Unusally depending on the organisation, capabilities and the mentor (Sensei).

Senior (Level III)

Now works as individual contributor with deep understandings. Ownership of technical components like SQL Server components. Key resource in problem analysis. Give process recommendations. Comprehensive knowledge of practices and organization structures, both formal and informal (goat paths).

Develop and present proposals, also to customers. Negotiate project and product technical terms and requirements with vendors and customers. Participate in pre-sales. Professional mentor (Sensei). Key business knowledge. Recognized for technical skills on international level with influence on wide organizational level.

Typically more than five years of experience. Often more than ten years.

Principal

Designing and presenting technical stratigies. Setting department goals and creating implementation plans. Setting and manging engineering budgets.  Develop and implement proces improvements.

Provide expert advise. People management in research and design. Professional and career mentor (Sensei) but not as primary priority. Deep business knowledge.

Now it is no longer a matter of years but more of capabilities and ambitions. Usually has at least a bachelor degree.

Master

Guiding and training professional teams and individuals. This is the primary focus. Deep knowledge and experience in both tech and processes. Can also be called Sensei in a Lean context.

Executive

Part of senior management. Set budgets. Define products and projects.

Driven by career ambitions.

Very rare title. Most people has left database administration many years before this level is relevant to pursue a more focused business career.

Inspiration

• SHIFTMAG: Engineering career levels explained.
• indeed: Principal Engineer vs. Senior Engineer.

History

2024-11-16: Post created.

2024-12-23: Master level added.

Reduce OS memory with BCDEdit

This is a quick description on how to reduce OS memory on virtual Windows Server 2016 from 128 GB to 48 GB without changing VM configuration. This can be done with the command BCDEdit.exe which is somewhat described by Microsoft in the documentation on bcdedit and the documents "BCDEdit Command-Line Options" and "BCDEdit Options Reference". The tool is a default tool in Microsoft Windows and is placed in the directory "C:\Windows\System32\".

The name of the tool referes to Boot Configuration Data, which is described somewhat in an old MSDN Vista document BCD.docx. Boot Configuration Data is a firmware independant data store with boot data. The store is placed in the system partition.

Reducing memory available to the OS is sometimes done, when you have an application that is not using all available memory, but is licensed by the memory amount available to the OS. And this is sometimes made worse by a computer with way too much memory that can't be reduced by ridiculous agreements.

Before

System Information (msinfo32.exe) on the guest VM. All installed memory is available to the OS:

Reduce OS Memory

Calculation: The number needed for the command is the amount of removed memory. In this case 128 GB - 48 GB = 80 GB. As the number must be in MB the corection is 80 GB * 1024 MB/GB = 81920 MB.

Start console (cmd.exe) as Administrator. PowerShell does not work. Enter the command line:

bcdedit /set {current} removememory 81920

• bcdedit is shorthand on BCDEdit.edit.
• /set is a option to BCDEdit described in BCDEdit /set.
• {current} is a reference to the current boot entry.
• removememory removes memory available to the Windows operating system.
• 81920 is the amount of memory to remove from the OS in MB. See calculation above.

Reboot VM.

After

System Information on guest VM:

Task Manager on guest VM:

It shows both 128 GB and 48 GB even only 48 GB is available to the OS. This will confuse most operators and administrators.

Roll-Back

Start console as Administrator. And enter the command line:

bcdedit /deletevalue {current} removememory

Reboot VM.

And all the memory is available to the VM as shown by System Information.

Notes on System Configuration

Windows has the standard tool System Configuration where it looks like you also can reduce OS memory. Let me point out that this does not work as bcdedit /set removememory but like bcdedit /set truncatememory which is not as effective.

Disclaimer

This is not my own findings but given to me by the good colleague Ib Thornøe (LinkedIn)