3

While deleting logins, there might be a possibility that there are users in databases along with chances that they own schemas in those databases.

Is there a way to find this out with a single select?

This is the warning message I get:

Deleting server logins does not delete the database users associated with the logins. To complete the process, delete the users in each database. It may be necessary to first transfer the ownership of schemas to new users.

enter image description here

1

We use below query to look for the logins mapped as user to specific databases. Hope it helps.

CREATE TABLE #tempww (
    LoginName nvarchar(max),
    DBname nvarchar(max),
    Username nvarchar(max), 
    AliasName nvarchar(max)
)

INSERT INTO #tempww 
EXEC master..sp_msloginmappings 

-- display results
SELECT * 
FROM   #tempww 
ORDER BY dbname, username

-- cleanup
DROP TABLE #tempww
1

The query below will return the schema owner information. You can change the JOIN to sys.database_principals to a LEFT JOIN to also return schema owners that are not logins, such as database roles and users without a login.

SELECT
      sp.name AS OwningLogin
    , dp.name AS OwningUser
    , s.name AS SchemaName
FROM sys.schemas AS s
JOIN sys.database_principals AS dp ON
    dp.principal_id = s.principal_id
JOIN sys.server_principals AS sp ON
    sp.sid = dp.sid;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.