Technical Article

How to retrieve roles from a Database

,

If you have environment where you restrict access to database objects based on user roles and if you have a lot of databases on different servers in the same domain.  The following script can generate roles from a given database in a format that can be used to create roles on different servers where the same(meaning a replicated or standby) database is housed :

Declare @usename SYSNAME

DECLARE usename CURSOR FOR SELECT S.NAME FROM sysusers S
where s.NAME NOT IN('PUBLIC','GUEST', 'DBO') AND NAME NOT LIKE  'DB%'

OPEN usename

FETCH NEXT FROM usename INTO @usename

PRINT 'SET QUOTED_IDENTIFIER OFF'
PRINT 'GO'
WHILE @@FETCH_STATUS=0
BEGIN
PRINT '--**************************************'
PRINT '--============' + @USENAME + '=========='
PRINT '--**************************************'
PRINT 'IF OBJECT_ID("dbo.' + @USENAME + '") IS NOT NULL '
PRINT SPACE(5) + 'BEGIN'
PRINT SPACE(10) + 'EXEC SP_DROPROLE "' + @USENAME + '"'
PRINT SPACE(10) + 'IF OBJECT_ID("dbo.'+ @USENAME + '") IS NOT NULL'
        PRINT SPACE(15) + 'PRINT "<<< FAILED DROPPING "' + @USENAME + '" >>>"'
    PRINT SPACE(10) +'ELSE' 
PRINT SPACE(15) + 'PRINT "<<< DROPPED ROLE "' + @USENAME + '" >>>"' 
PRINT SPACE(5) + 'END'
PRINT 'GO'
PRINT 'EXEC SP_ADDROLE "' + @USENAME + '"'
PRINT 'GO'

FETCH NEXT FROM usename 
INTO @usename
END
CLOSE usename
DEALLOCATE usename
PRINT '--**************************************'
PRINT 'SET QUOTED_IDENTIFIER ON'
PRINT 'GO'

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating