Solved

execute sp_droprolemember foreach db

Posted on 2014-12-14
2
142 Views
Last Modified: 2014-12-30
Hello,

I search a script for execute sp_droprolemember on all databases.

Thanks
0
Comment
Question by:bibi92
2 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 40499627
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
 

Author Closing Comment

by:bibi92
ID: 40523343
Thanks
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now