Solved

Revoking all permissions from all user defined roles

Posted on 2004-08-29
10
478 Views
Last Modified: 2012-06-27
Is there any way to revoke all permissions from all user defined roles, without knowing the roles or objects?  I know how to do it when I know the name of the role and object, but am looking for a way to clear all permissions so I can reset all of them.

Thanks!
Teiwaz
0
Comment
Question by:teiwaz
  • 5
  • 5
10 Comments
 
LVL 6

Accepted Solution

by:
robertjbarker earned 500 total points
ID: 11926744
You can get a list of all user defined roles with the query:

select [name] from mydatabase.dbo.sysusers
  where issqlrole = 1 and gid <> 0 or isapprole = 1

If you wish to exclude application roles you can use:

select [name] from mydatabase.dbo.sysusers
  where issqlrole = 1 and gid <> 0

Then use what you always used to revoke permissions on these roles.
0
 
LVL 6

Expert Comment

by:robertjbarker
ID: 11926763
To revoke all permissions granted to a role you can use "revoke all from <role>". That way you don't need to know the object names.
0
 
LVL 6

Expert Comment

by:robertjbarker
ID: 11926893
Please ignore the "revoke all from <role>" comment. That does not seem to be working.
So all that is usefull right now is getting the list of roles. Sorry.

Working...
0
 
LVL 1

Author Comment

by:teiwaz
ID: 11926901
"revoke all from <role>" isn't working.  I.e. I execute this statement , the run sp_helprotect and all the role's permissions are still there.  Also, I can still log on as a user of this role (with no permissions explicitly set) and still access data.  Any idea why?
0
 
LVL 1

Author Comment

by:teiwaz
ID: 11926907
Oops, when I submitted my last comment, I got your comment to the same effect.  I'm looking into the sp_helprotect sproc to see how to get a list of objects for a role.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 6

Expert Comment

by:robertjbarker
ID: 11926985
OK, sorry for the false starts.
If you want to revoke all permissions on user created roles, then how about dropping all such roles and then recreating them, thus:

create table #role_names ([name] varchar(255))

insert #role_names
select [name] from pmdbreports.dbo.sysusers
  where issqlrole = 1 and gid <> 0

declare @name nvarchar(40)
declare @sql nvarchar(255)

declare roll_names cursor for
 select [name] from #role_names

open roll_names
FETCH NEXT FROM roll_names INTO @name

WHILE @@FETCH_STATUS = 0
 BEGIN
  set @sql = 'sp_droprole ''' + @name + ''''
  print 'drop ' + @sql
  exec sp_executesql @sql
  set @sql = 'sp_addrole ''' + @name + ''''
  print 'add ' + @sql
  exec sp_executesql @sql
  FETCH NEXT FROM roll_names INTO  @name
 END
CLOSE roll_names
DEALLOCATE roll_names

drop table #role_names
0
 
LVL 1

Author Comment

by:teiwaz
ID: 11927018
The problem I see with this approach is that then I'd have to handle storing away users in those roles, and adding them back to the roles.

I'm close on an alteration of sp_helprotect to get a list of objects belonging to a role.  I'll post what I figure out.
0
 
LVL 1

Author Comment

by:teiwaz
ID: 11927146
OK, figured out how to do this, with your help.  Here is the script.

Declare @SQL varchar(500)
Declare @MoreRoles bit
Declare @CurrRole sysname
Declare @CurrObject varchar(128)

Declare DbRoles Cursor
For
    Select [name]
    From sysusers
    Where isSQLRole=1 and gid<>0

Open DbRoles
Fetch Next From DbRoles Into @CurrRole
While @@FETCH_STATUS=0
Begin
    Print @CurrRole + '...'
    -- For each role, loop through all objects and drop them
    Declare RoleObjects Cursor
    For
        Select Distinct object_name(id)
        From sysprotects
        Where user_name(uid)=@CurrRole

    Open RoleObjects
    Fetch Next From RoleObjects Into @CurrObject
    While @@FETCH_STATUS=0
    Begin
        Print 'Revoking right on ' + @CurrObject
        -- Drop all permissions for this object
        Set @SQL = 'Revoke All On ' + @CurrObject + ' FROM ' + @CurrRole
        Exec(@SQL)

        -- Get next object
        Fetch Next From RoleObjects Into @CurrObject
    End
   
    -- Clean up this cursor
    Close RoleObjects
    Deallocate RoleObjects

    -- Get next role
    Fetch Next From DbRoles Into @CurrRole
End    

Close DbRoles
Deallocate DbRoles
GO
0
 
LVL 1

Author Comment

by:teiwaz
ID: 11927174
Oh, the part from sp_helprotect is:

Select Distinct object_name(id)
From sysprotects
Where user_name(uid)=@CurrRole

Though sp_helprotect is a huge procedure, it turns out that this is the salient piece and its simple. (dont' look for this in sp_helprotect, this is from the bit that loads the temp table. The distinct is needed to remove duplicates.)
0
 
LVL 6

Expert Comment

by:robertjbarker
ID: 11927223
thank you!

for the points and the final info.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

747 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

10 Experts available now in Live!

Get 1:1 Help Now