execute sp_droprolemember foreach db

Hello,

I search a script for execute sp_droprolemember on all databases.

Thanks
bibi92Asked:
Who is Participating?
 
chaauCommented:
According to MSDN:
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER ROLE instead.
So, I recommend you use ALTER ROLE. To execute the script for each database use the following cursor:
DECLARE @DB_Name varchar(100)
DECLARE @Command nvarchar(200)

DECLARE database_cursor CURSOR FOR
SELECT name
FROM MASTER.sys.sysdatabases

OPEN database_cursor

FETCH NEXT FROM database_cursor INTO @DB_Name

WHILE @@FETCH_STATUS = 0
BEGIN
     SELECT @Command = 'use '+ @DB_Name +' ALTER ROLE Sales DROP MEMBER Barry'
     EXEC sp_executesql @Command

     FETCH NEXT FROM database_cursor INTO @DB_Name
END

CLOSE database_cursor
DEALLOCATE database_cursor 

Open in new window

0
 
bibi92Author Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.