Post reply

easy process to revoke?

  • October 5, 2010 at 1:22 pm

    #142224

    I have 28 users who has Read/Write/ddl_admin previalges on 450 databases, Now i want to give them only read access revoking Write and ddl_admin, is there an easy process to do this across the server.

  • October 5, 2010 at 3:16 pm

    #1232098

    any scripts to help this from the forum members?

  • October 5, 2010 at 3:35 pm

    #1232109

    Tara-1044200 (10/5/2010)

    any scripts to help this from the forum members?

    Direct script, no, but this will help with the across the server bit:

    sp_MSforeachDB

    http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm

    You can run the same script across all db's on the server that way. Now you need to REVOKE data_writer and REVOKE ddl_admin for each user. so you'd have something like :

    EXEC sp_MSforeachDB @command1='REVOKE ALL FROM <username> CASCADE', @command2 = 'GRANT data_reader TO <username>'

    Allow for slightly untested syntax here, I'm not running this puppy locally. 🙂

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply