Sql-server – How to identify database users and their owned schemas before dropping server logins

loginspermissionssql server

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.

Best Answer

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

Related Solutions

Sql-server – Unable to delete SQL Server 2008 R2 login

If you have many jobs and do not know which job is owned by this login, this query will tell you the name of jobs owned by them:

SELECT j.name
FROM msdb.dbo.sysjobs AS j
INNER JOIN sys.syslogins AS l ON j.owner_sid = l.sid
WHERE l.name = 'loginYouWantToDelete'

Once you have found the job, you can delete it using: sp_delete_job: (From MSDN)

EXEC msdb.dbo.sp_delete_job
    @job_name = N'JobToDelete';

Sql-server – How to link users and logins in an Availability Group

What you will have to do is connect to the instance that has a mis-matched SID, and you'll have to recreate the login and specify an explicit SID. For instance, on the instance where you have the orphaned user and the following returns the user:

exec sp_change_users_login 'report';
go

Copy the SID from the column UserSID. And if you already have an existing login that you want to preserve the name from on that instance, you can't. There is no way to ALTER LOGIN and change the SID. So, you will need to drop the login and recreate it:

drop login YourLogin;
go

create login YourLogin
with
    password = 'password',
    check_policy = off,     -- simple password and no check policy for example only
    sid = 0xC26909...................;
go

Again, you will want to set the sid parameter of CREATE LOGIN to the UserSID you retrieved from executing sp_change_users_login with the report.

Note: You will want to set the password on this/these replica(s) to the same so that you don't have a password mismatch between replicas.

I am assuming that your login(s) in question here are SQL Server logins, as this shouldn't be a problem for Windows Auth domain accounts because the SIDs should be the same on each instance.

You could also leverage contained databases here and have the database principal authenticate at the database level. That way you wouldn't have to worry about orphaned users.

Here is some documentation on this:
BOL reference on Management of Logins and Jobs for the Databases of an Availability Group
KB article on How to transfer logins and passwords between instances of SQL Server

Also, this should go without saying, but test this out in a development/QA environment to verify and ensure proper functionality before hitting production.

Best Answer

Related Solutions

Sql-server – Unable to delete SQL Server 2008 R2 login

Sql-server – How to link users and logins in an Availability Group

Related Question