February 2024 – Run SQL Server BLOG

15th February 202416th February 2024

Install Oracle in docker

Install Oracle express

docker command in windows powershell (WSL installed):

docker login container-registry.oracle.com
docker container create -it   --name oracletest -p 1521:1521 -e ORACLE_PWD=alma123  container-registry.oracle.com/database/express:latest
docker start oracletest

# chek it later
docker exec oracletest env

docker login container-registry.oracle.com

docker container create -it --name oracletest -p 1521:1521 -e ORACLE_PWD=alma123 container-registry.oracle.com/database/express:latest

docker start oracletest

# chek it later

docker exec oracletest env

download oracle sqldeveloper Windows 64-bit with JDK 11 included:

https://www.oracle.com/database/sqldeveloper/technologies/download/

14th February 2024

Add read rights to all existing and new user databases

The following script add read user rights for every user databases and system model databases for the new databases to readeruser login.

USE [master]
GO
CREATE LOGIN [readeruser] WITH PASSWORD=N'supersecretpw', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO


DECLARE @dbname VARCHAR(50)   
DECLARE @statement NVARCHAR(max)

DECLARE db_cursor CURSOR 
LOCAL FAST_FORWARD
FOR  
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','msdb','tempdb','distribution')  
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @dbname  
WHILE @@FETCH_STATUS = 0  
BEGIN  

SELECT @statement = 'use '+@dbname +';'+ 'CREATE USER [readeruser] FOR LOGIN [readeruser]; EXEC sp_addrolemember N''db_datareader'', [readeruser];'


EXEC sp_executesql @statement


FETCH NEXT FROM db_cursor INTO @dbname  
END  
CLOSE db_cursor  
DEALLOCATE db_cursor

USE [master]

CREATE LOGIN [readeruser] WITH PASSWORD=N'supersecretpw', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON

DECLARE @dbname VARCHAR(50)

DECLARE @statement NVARCHAR(max)

DECLARE db_cursor CURSOR

LOCAL FAST_FORWARD

FOR

SELECT name

FROM MASTER.dbo.sysdatabases

WHERE name NOT IN ('master','msdb','tempdb','distribution')

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @dbname

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @statement = 'use '+@dbname +';'+ 'CREATE USER [readeruser] FOR LOGIN [readeruser]; EXEC sp_addrolemember N''db_datareader'', [readeruser];'

EXEC sp_executesql @statement

FETCH NEXT FROM db_cursor INTO @dbname

END

CLOSE db_cursor

DEALLOCATE db_cursor

13th February 2024

Resolve SID to domain account

There are reasons you need to resolve SID. You can do it by powershell:

$SID = New-Object System.Security.Principal.SecurityIdentifier("S-1-5-21-9534051626-2041855937-2114229915-98301")
$objUser = $SID.Translate([System.Security.Principal.NTAccount])
$objUser.Value

$SID = New-Object System.Security.Principal.SecurityIdentifier("S-1-5-21-9534051626-2041855937-2114229915-98301")

$objUser = $SID.Translate([System.Security.Principal.NTAccount])

$objUser.Value