Thursday, February 16, 2017

Remap the orphaned database users

I found few database users (SQL users) with no login in the database. These are orphaned users

The following query is run in the database context

-- SQL to run to identify users without login :
 
 
SELECT CASE WHEN DATALENGTH(sid) = 28

AND type = 'S' -- only want SQL users

AND principal_id > 4 -- ignore built in users

THEN 1 ELSE 0 END AS is_user_without_login,*

FROM sys.database_principals


--I found the user where is_user_without_login = 1

--dropped the user
--and created the user linking the user to the login

use db_01
GO

drop user dbuser1
create user dbuser1 for login  svrlogin1

No comments:

Post a Comment