Solved

Revoking all permissions from all user defined roles

Posted on 2004-08-29
10
479 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

911 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

21 Experts available now in Live!

Get 1:1 Help Now