SSIS error: Drop faield for user

I was trying to drop a user from SSIDB database and I encountered the following error -

The database principal has granted or denied permission to catalog objects in the database and cannot be dropped. The transaction ended in the trigger. The batch has been aborted. (Microsoft SQL Server, Error: 27226)


I ran the following query to find the permission of the user I am trying to drop


use SSISDB
GO

SELECT TOP 1000 [object_type]

,[object_id]

,[principal_id]

,[permission_type]

,[is_deny]

,[grantor_id]

, 'EXEC catalog.revoke_permission @object_type=' + CAST([object_type] AS VARCHAR)

+ ', @object_id=' + CAST([object_id] AS VARCHAR)

+ ', @principal_id=' + CAST(principal_id AS VARCHAR)

+ ', @permission_type=' + CAST(permission_type AS VARCHAR) [execute this]

FROM [SSISDB].[catalog].[explicit_object_permissions]

WHERE principal_id = USER_ID('usertodrop')


Then copied the contents of the [execute this] column in a new query window and executed it against the SSISDB database which revokes the permission .
Then I was able to drop the user.

List permission on a databse objects

use current_userdb
GO

select o.name , pm.permission_name,pr.name, user_name(pm.grantee_principal_id) from sys.database_permissions pm inner join

sys.database_principals pr

on pm.grantee_principal_id = pr.principal_id

inner join sys.objects o

on o.object_id = pm.major_id

where object_name(pm.major_id) = 'object1'

Work Adventure: Fix Orphaned user

When I restored a database to a new server, the users were imported but the login were not. The database users were  imported as the orphan users in SQL 2012

Here is how I found the orphaned users in a database

EXEC sp_change_users_login @Action='Report'

--OR we can run the following in the current database context

select * from sysusers where issqluser = 1 and (sid <> 0x0

and sid is not null)

and (LEN(sid) <= 16 ) AND suser_sname(sid) is null 





-----------------------------------------

The in order to link the orphan user to the existing login  I did the following


sp_change_users_login 'Update_One', 'OrphanUser, 'Logintolinkto'

 

Schema: change schema of a user

The schema of a user was associated to a different schema instead of the default schema dbo. to check the user who do not have the default schema as dbo
select 'ALTER USER [' + name + '] with default_schema = [dbo]'
from sys.database_principals
where type in ('U')
and default_schema_name not in ('dbo')
--the above lists and generates a alter script
ALTER USER [Dom1\user1] with default_schema = [dbo]


--change the schema of the stored procs
SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + p.Name
FROM sys.Procedures p
INNER JOIN
sys.Schemas s on p.schema_id = s.schema_id
WHERE s.Name = 'CHANGE_ME_Username'