I see a lot of GRANT
and DENY
records in table sys.database_permissions
for a specific user.
How can I delete/clear all these entries?
I need to clear to run a script that will do the appropriate GRANT
and DENY
.
I can't simply delete the user and recreate. This view does not accept modifications, can't simply run a DELETE
.
Thank you.
Seems there is no better way than doing it manually:
DECLARE cursor_roles CURSOR LOCAL FAST_FORWARD FOR
SELECT dbrole.name as role_name, dbuser.name as user_name
FROM sys.database_role_members rm
INNER JOIN sys.database_principals dbrole ON dbrole.principal_id = rm.role_principal_id
INNER JOIN sys.database_principals dbuser ON dbuser.principal_id = rm.member_principal_id
WHERE dbuser.name in (@MYUSER)
OPEN cursor_roles
DECLARE @role_name nvarchar(100), @user_name_in_role nvarchar(100)
FETCH NEXT FROM cursor_roles INTO @role_name, @user_name_in_role
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('ALTER ROLE ' + @role_name + ' DROP MEMBER ' + @user_name_in_role)
FETCH NEXT FROM cursor_roles INTO @role_name, @user_name_in_role
END
CLOSE cursor_roles
DEALLOCATE cursor_roles