question

Hello,

Setting up a new server, I came to an unexpected behaviour.

When I create new users on my database, the user inherits all accesses of db_owner while usually, on older servers, it only has few access rights.

I am wondering how I could have get to this situation.

As described here https://ask.sqlservercentral.com/questions/164649/dropped-default-schema-by-mistake-unexpected-resul.html, I did dropped default schemas at the beginning by mistake, could it be linked?

Any idea how I can set it back and fix this?

To compare, I ran this script on “bad” and “good” servers and attached the results:

EXECUTE AS USER ‘basicTestUser’
GO
USE myDb
GO
SELECT * FROM sys.fn_my_permissions(null, ‘server’)
GO

EXECUTE AS USER ‘basicTestUser’
GO
USE myDb
GO
SELECT * FROM sys.fn_my_permissions(null, ‘database’)
GO 

EXECUTE AS USER ‘basicTestUser’
GO
SELECT * FROM sys.fn_my_permissions(‘dbo’, ‘schema’)
revert;

Hoping you’ll be able to help me, regards,

Claude

** GOOD **

** BAD **