Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sp_help_revlogin + Server Roles Expand / Collapse
Author
Message
Posted Sunday, January 11, 2004 8:12 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, May 25, 2014 5:32 AM
Points: 71, Visits: 108

Hi All,

I followed the suggestions in the KB article on how to backup user names and passwords...and all is good. However, after running through disaster recovery on a new server I found that any Server roles that the user had were lost. Is there any script which will allow me to also copy the users Server Roles??

Thanks In Advance,

Terry




Post #94605
Posted Monday, January 12, 2004 11:28 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 8, 2009 10:17 AM
Points: 727, Visits: 19

Here's one that I wrote...

By the way, this will only work for SQL 2000, it will need minor modifications for 7.0

 

-- af_revLoginAttrib_2k

-- Verify existence of af_revLoginAttrib_2k procedure.  If it does exist,
-- drop it, so that it can be re-created.
if exists (select * from sysobjects where id = object_id(N'[dbo].[af_revLoginAttrib_2k]') and
 OBJECTPROPERTY(id, N'IsProcedure') = 1)
 begin
  print 'dropping existing af_revLoginAttrib_2k procedure.'
  drop procedure [dbo].[af_revLoginAttrib_2k]
  print 'creating af_revLoginAttrib_2k procedure.'
 end
GO

create procedure af_revLoginAttrib_2k

as

/*************************************************************/                                                                                                                                                     
--                                                  
-- Module Name: af_revLoginAttrib_2k
--                                               
-- Description:
-- The purpose of this procedure is to script the attributes
-- (default db, roles, etc) associated with the login-ids on a server.
--                                                
-- Written By: Steve Phelps           
--                                                 
-- Date: July 15, 2003          
--                                                  
-- Modified :                                      
-- Date:                                           
--                                                 
-- USAGE:                                           
--
--  exec af_revLoginAttrib_2k
--                           
/*************************************************************/  

declare
  @command varchar(500),
 @name    varchar(30),
 @dbname   varchar(128),
 @DenyLogin  int,
 @HasAccess  int,
 @IsNTName  int,
 @IsNTGroup  int,
 @IsNTUser  int,
 @SysAdmin  int,
 @SecurityAdmin int,
 @ServerAdmin int,
 @SetupAdmin  int,
 @ProcessAdmin int,
 @DiskAdmin  int,
 @DBCreator  int,
 @BulkAdmin  int
 
declare login_cur cursor for
 select
   name, dbname, denylogin, hasaccess, isntname,
   isntgroup, isntuser, sysadmin, securityadmin, serveradmin,
   setupadmin, processadmin, diskadmin, dbcreator, bulkadmin
  from master..syslogins
  where name not in ('BUILTIN\Administrators', 'sa', 'SA')
  order by name

open login_cur
fetch next from login_cur into
 @name,   
 @dbname,  
 @DenyLogin, 
 @HasAccess, 
 @IsNTName, 
 @IsNTGroup, 
 @IsNTUser, 
 @SysAdmin, 
 @SecurityAdmin,
 @ServerAdmin,
 @SetupAdmin, 
 @ProcessAdmin,
 @DiskAdmin, 
 @DBCreator, 
 @BulkAdmin 

if (@@fetch_status = -1)
 begin
  close login_cur
  deallocate login_cur
--  return -1
 end
else
 begin
  
  while (@@fetch_status <> -1)
   begin
    if (@@fetch_status <> -2)
     begin

--     begin processing current user.
      print ' '
      print '-- processing attributes for id = ' + @name

--     set the default database.
      select @command = 'exec sp_defaultdb ' + char(39) + @name +
       char(39) + ', ' + char(39) + @dbname + char(39)
      print @command

--     add user to applicable roles.
      if @SysAdmin = 1
       BEGIN
        select @command = 'exec sp_addsrvrolemember ' +
         char(39) + @name + char(39) + ', ' +
         char(39) + 'sysadmin' + char(39)
        print @command
       END
      else
       if @SecurityAdmin = 1
        begin
         select @command = 'exec sp_addsrvrolemember ' +
          char(39) + @name + char(39) + ', ' +
          char(39) + 'securityadmin' + char(39)
         print @command
        end
       if @ServerAdmin = 1
        begin
         select @command = 'exec sp_addsrvrolemember ' +
          char(39) + @name + char(39) + ', ' +
          char(39) + 'serveradmin' + char(39)
         print @command
        end
       if @SetupAdmin = 1
        begin
         select @command = 'exec sp_addsrvrolemember ' +
          char(39) + @name + char(39) + ', ' +
          char(39) + 'setupadmin' + char(39)
         print @command
        end
       if @ProcessAdmin = 1
        begin
         select @command = 'exec sp_addsrvrolemember ' +
          char(39) + @name + char(39) + ', ' +
          char(39) + 'processadmin' + char(39)
         print @command
        end
       if @DiskAdmin = 1
        begin
         select @command = 'exec sp_addsrvrolemember ' +
          char(39) + @name + char(39) + ', ' +
          char(39) + 'diskadmin' + char(39)
         print @command
        end
       if @DBCreator = 1
        begin
         select @command = 'exec sp_addsrvrolemember ' +
          char(39) + @name + char(39) + ', ' +
          char(39) + 'dbcreator' + char(39)
         print @command
        end
       if @BulkAdmin = 1
        begin
         select @command = 'exec sp_addsrvrolemember ' +
          char(39) + @name + char(39) + ', ' +
          char(39) + 'bulkadmin' + char(39)
         print @command
        end
     end -- if (@@fetch_status <> -2)
    fetch next from login_cur into
     @name,   
     @dbname,  
     @DenyLogin, 
     @HasAccess, 
     @IsNTName, 
     @IsNTGroup, 
     @IsNTUser, 
     @SysAdmin, 
     @SecurityAdmin,
     @ServerAdmin,
     @SetupAdmin, 
     @ProcessAdmin,
     @DiskAdmin, 
     @DBCreator, 
     @BulkAdmin 
   end -- while (@@fetch_status <> -1)
   close login_cur
   deallocate login_cur
 end -- (@@fetch_status = -1)


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 




Post #94685
Posted Friday, February 21, 2014 2:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 8:32 AM
Points: 9, Visits: 141
Did you find a solution? I have the same problem. I am using transferring login from SQL Server 2008 r2 to 2012
Post #1544197
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse