Get User Info

**************************************************************** *//* Name        : GetUserInfo                                        *//*                                                                  *//* Author      : Bob Schaefer                                       */                                                        *//* Description : Generates database user scripts.                   *//*               - adding roles                                     *//*               - adding users                                     *//*               - adding role members                              *//*               - granting user & role permissions                 *//* **************************************************************** */
use tempdb
if exists (select * 
           from dbo.sysobjects 
           where id = object_id(N'[dbo].[wrkxx]') 
             and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[wrkxx]

CREATE TABLE [DBO].[WRKxx] (
[msg] [char] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) 

use master

-- Declare variable(s)
Declare @rtn int
declare @dbname        sysname
set @dbname = 'QNXT_INTERFACE_QA1'           --   <<<-- Change Database Name

insert tempdb.dbo.wrkxx (msg)
select ' -- Server: ' + @@servername
insert tempdb.dbo.wrkxx (msg)
select ' -- Database: ' + @dbname
insert tempdb.dbo.wrkxx (msg)
select ' -- Date captured: ' + convert(varchar(26), GetDate(),113)
insert tempdb.dbo.wrkxx (msg)
select ''
insert tempdb.dbo.wrkxx (msg)
select ' -- Script for restoring database user info...'
insert tempdb.dbo.wrkxx (msg)
select ''

--Getting database user info
insert tempdb.dbo.wrkxx (msg)
select 'use ' + @dbname
insert tempdb.dbo.wrkxx (msg)
select ''
exec( 'If exists(select * ' +
                  'from ' + @dbname + '.dbo.sysusers ' +
                 'where sid not in(select sid from master.dbo.syslogins) ' +
                    'and name <> ''guest'') ' +
         'Begin ' +
            'insert tempdb.dbo.wrkxx (msg)  Select ''-- These users have sids that are different than their login sids:'' ' +
            'insert tempdb.dbo.wrkxx (msg)  Select ''-- '' + name ' +
              'from ' + @dbname + '.dbo.sysusers ' +
             'where sid not in(select sid from master.dbo.syslogins) ' +
               'and name <> ''guest'' ' +
         'End')

insert tempdb.dbo.wrkxx (msg)
select ''
insert tempdb.dbo.wrkxx (msg)
select ' -- Script for adding roles'
exec( 'insert tempdb.dbo.wrkxx (msg) select ''     exec sp_addrole N'''''' + name + ''''''''' + 
      ' from ' + @dbname + 
      '.dbo.sysusers where uid > 16393 order by name')

insert tempdb.dbo.wrkxx (msg)
select ''
insert tempdb.dbo.wrkxx (msg)
select ' -- Script for adding users'
exec( 'insert tempdb.dbo.wrkxx (msg)  select ''     exec sp_grantdbaccess N'''''' + b.loginname + '''''',N'''''' + a.name + ''''''''' + 
      ' from ' + @dbname + 
      '.dbo.sysusers a join master.dbo.syslogins b on a.sid = b.sid where a.uid > 3 and a.uid < 16384 order by b.loginname')

insert tempdb.dbo.wrkxx (msg)
select ''
insert tempdb.dbo.wrkxx (msg)
select ' -- Script for adding role members'
exec( 'insert tempdb.dbo.wrkxx (msg) select ''     exec sp_addrolemember N'''''' + b.name + '''''',N'''''' + a.name + ''''''''' + 
      ' from ' + @dbname + 
      '.dbo.sysusers a, ' +
      @dbname + '.dbo.sysusers b, ' +
      @dbname + '.dbo.sysmembers c ' +
      'where a.uid = c.memberuid ' +
      'and a.uid > 3 ' + --and a.uid < 16384 ' +
      'and b.uid = c.groupuid ' +
      'order by a.name')

insert tempdb.dbo.wrkxx (msg)
select ''
insert tempdb.dbo.wrkxx (msg)
select ' -- Script for granting user & role permissions'
Exec ('insert tempdb.dbo.wrkxx (msg) select case when action = 26  then ''     Grant REFERENCES'' ' +
                   'when action = 193 then ''     Grant SELECT'' ' +
                   'when action = 195 then ''     Grant INSERT'' ' +
                   'when action = 196 then ''     Grant DELETE'' ' +
                   'when action = 197 then ''     Grant UPDATE'' ' +
                   'when action = 198 then ''     Grant CREATE TABLE'' ' +
                   'when action = 203 then ''     Grant CREATE DATABASE'' ' +
                   'when action = 207 then ''     Grant CREATE VIEW'' ' +
                   'when action = 222 then ''     Grant CREATE PROCEDURE'' ' +
                   'when action = 224 then ''     Grant EXECUTE'' ' +
                   'when action = 228 then ''     Grant DUMP DATABASE'' ' +
                   'when action = 233 then ''     Grant CREATE DEFAULT'' ' +
                   'when action = 235 then ''     Grant DUMP TRANSACTION'' ' +
                   'when action = 236 then ''     Grant CREATE RULE'' ' +
                   'else '''' ' +
              'end + ' +
              ''' on ['' + d.name + ''].['' + b.name + ''] to ['' + c.name + '']'' + ''   go'' ' +
       'from ' + @dbname + '.dbo.sysprotects a,  ' +
            @dbname + '.dbo.sysobjects b,  ' +
            @dbname + '.dbo.sysusers c,  ' +
            @dbname + '.dbo.sysusers d  ' +
       'where a.id = b.id  ' +
         'and a.uid = c.uid ' + 
         'and a.uid >= 0  ' +
         'and a.protecttype = 205 ' +
         'and b.uid = d.uid ' + 
         'and b.xtype <> ''S'' ' + 
         'and b.status >= 0 ' + 
       'order by c.name, ' + 
                'b.name,  ' +
                'action ')
use tempdb

Select *
from tempdb.dbo.wrkxx

drop table tempDB.dbo.wrkxx