November 13, 2015 at 3:14 pm
Hi Experts ,
I am using below script to drop Database roles for refresh process while I am trying to do that I am getting below error .
The role has members. It must be empty before it can be dropped.
Can any one guild me what I can fix to make it work.
I am using below script..
-----------------------------------------------------------
-- drop/create logins, add to roles
-----------------------------------------------------------
EXEC sp_foreachdb
@command = '
USE ?;
DECLARE @user VARCHAR(100),
@role VARCHAR(100),
@type VARCHAR(100),
@sql VARCHAR(5000),
@lastuser VARCHAR(100),
@schema VARCHAR(128),
@object VARCHAR(128)
SET @lastuser = ''XXX''
---- schemas -------------------------------------------------------------------------------------
---- drop custom schema objects for other than audit
IF EXISTS(
SELECT o.name
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE s.NAME NOT IN (''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'',''audit'')
AND s.NAME NOT LIKE ''db_%''
)
BEGIN
DECLARE c_schemas CURSOR
FOR
SELECT o.name , s.name , o.type
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE s.NAME NOT IN (''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'',''audit'')
AND s.NAME NOT LIKE ''db_%''
OPEN c_schemas
FETCH NEXT FROM c_schemas INTO @object, @schema, @type
WHILE @@FETCH_STATUS = 0
BEGIN
IF @type = ''U''
SET @sql = ''DROP TABLE ''
ELSE
IF @type = ''V''
SET @sql = ''DROP VIEW ''
ELSE
IF @type = ''P''
SET @sql = ''DROP PROC ''
ELSE
IF @type = ''FN''
SET @sql = ''DROP FUNCTION ''
ELSE
SET @sql = ''''
IF LEFT(@sql, 4) = ''DROP''
BEGIN
SET @sql = @sql + ''['' + @schema + ''].'' + @object
PRINT @sql
EXEC(@sql)
END
ELSE
PRINT ''DIDN''''T DROP: '' + @object + @schema + @type
FETCH NEXT FROM c_schemas INTO @object, @schema, @type
END
CLOSE c_schemas
DEALLOCATE c_schemas
END
---- drop other custom schemas ----
IF EXISTS(
SELECT s.name
FROM sys.schemas s
WHERE s.NAME NOT IN (''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'',''audit'')
AND s.NAME NOT LIKE ''db_%''
)
BEGIN
DECLARE c_schemas CURSOR
FOR
SELECT s.name
FROM sys.schemas s
WHERE s.NAME NOT IN (''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'',''audit'')
AND s.NAME NOT LIKE ''db_%''
OPEN c_schemas
FETCH NEXT FROM c_schemas INTO @schema
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ''DROP SCHEMA ''
SET @sql = @sql + ''['' + @schema + '']''
PRINT @sql
EXEC(@sql)
FETCH NEXT FROM c_schemas INTO @schema
END
CLOSE c_schemas
DEALLOCATE c_schemas
END
ELSE
PRINT '' -- NO CUSTOM SCHEMAS TO DROP''
---- roles ----------------------------------------------------------------------------------------------------------------
---- drop existing role memberships ----
declare @role_id VARCHAR(100),
@role_member VARCHAR(100)
WHILE EXISTS(
SELECT p.name
FROM sys.database_role_members m
JOIN sys.database_principals p
ON m.member_principal_id = p.principal_id
WHERE principal_id between 5 and 15999
AND p.type = ''R''
)
BEGIN
SELECT TOP 1
@role_member = p.name, @role_id = m.role_principal_id
FROM sys.database_role_members m
JOIN sys.database_principals p
ON m.member_principal_id = p.principal_id
WHERE principal_id between 5 and 15999
AND p.type = ''R''
SELECT @role = p.name
FROM sys.database_principals p
WHERE p.principal_id = @role_id
SET @sql = ''sp_droprolemember '' + @role + '', '' + @role_member
PRINT @sql
EXEC(@sql)
END
---- drop existing roles ----
WHILE EXISTS (
SELECT name
FROM sys.database_principals p
WHERE principal_id BETWEEN 5 AND 15999
and (p.type = ''R'')
--OR p.type = ''A'')
)
BEGIN
SELECT TOP 1
@role = name
FROM sys.database_principals p
WHERE principal_id BETWEEN 5 AND 15999
and (p.type = ''R'')
-- OR p.type = ''A'')
SET @sql = ''sp_droprole '' + @role
PRINT @sql
EXEC(@sql)
END
---- users ---------------------------------------------------------------------
---- drop existing users ----
DECLARE c_users CURSOR
FOR
SELECT name
FROM sys.database_principals
WHERE type NOT IN( ''A'', ''R'')
AND principal_id BETWEEN 5 AND 15999
AND name <> ''audit''
OPEN c_users
FETCH NEXT FROM c_users INTO @user
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ''DROP USER ['' + @user + '']''
PRINT @sql
EXEC (@sql)
IF @@error <> 0
PRINT ''Problem dropping user - '' + @user
FETCH NEXT FROM c_users INTO @user
END
CLOSE c_users
DEALLOCATE c_users
',
@database_list = 'Database Name',
@suppress_quotename = 1;
Thanks for your help
November 13, 2015 at 8:01 pm
Quick question, when emptying the roles of users, do you check for any ownership of those users?
😎
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply