Script map user to DB during install

1

Hello

i've a script to map the user NT AUTHORITY\SYSTEM (depending the language, NT AUTHORITY could be different therefore my like % condition) and map this user as DB_Owner to my db AlarmDB :

DECLARE @username VARCHAR(255);
DECLARE @tblName TABLE (name VARCHAR(255));

INSERT INTO @tblName SELECT TOP(1) [name] FROM [sys].[server_principals] WHERE type = 'U' AND is_disabled = 0 AND name LIKE '%\SYSTEM'

IF EXISTS (SELECT TOP(1) [name] FROM @tblName)
Begin
	SET @username = (SELECT TOP(1) [name] FROM @tblName);	
	USE AlarmDB
	EXEC sp_addrolemember 'db_owner', @username
end

the script looks not working, i've the checkbox "db_owner" who is checked but not mapped to my DB "AlarmDB" , and in AlarmDB i've a red cross on the user NT AUTHORITY\SYSTEM. Do you have an idea why ? thanks.

Here the image of the config after the script execution :

sql
sql933×661 38.4 KB

I've to solve it : delete user from the DB (security) and map again.

2

up? thanks

3

nt authority is a Local System account.

It isn't intended to own Databases

4

I think nt authority has full access anyways.

I tried your script, and according to Database -> Security -> Users, it worked. Though in Users it appears as disabled, similar to the User 'sys'. Likely since these are system accounts