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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy