Technical Article

Extract User-Role Mapping

,

This script can be used with SQL 2000 and 2005. For example if you have 10 users in current database, it will retrieve all Group information along with default database, if the user has a login account else if will return a blank.

Begin
--Create a temp table that will hold the main result. This script will check your version, if SQL 2005 or 2000,
--because  sp_helpuser return 6 values in SQL 2000 and 7 values in SQL 2005.
    Create table #tmpUserPerm
    (UserName varchar(100),
    GroupName varchar(100),
    LoginName varchar(100),
    DefDBName varchar(100),
    UserId int,
    SID varbinary(100),
    DefSchemaName varchar(100) null)

    Declare @name varchar(100)
    Declare @ver varchar(100)

    --Create a temp table that will store all users except DBO and GUEST. If you want all users then 
    --you can remove "and name not in ('DBO', 'GUEST')" from the following statement.


    select uid, name into #TmpUser from sysusers 
    where issqluser = 1 and hasdbaccess <> 0 and name not in ('DBO', 'GUEST')

    --Execute the below query to get current version of SQL SERVER
    set @ver = convert(varchar(100),SERVERPROPERTY('productversion'))

    if (@ver = '9.00.3054.00') --If SQL 2005 then 
    begin
--Run a cursor for all users
       declare cur Cursor for Select name from #Tmpuser
       open cur
       fetch next from cur into @name
       while @@fetch_Status = 0
       BEGIN
   --Get data from sp_helpuser for current value of user (@NAME)
           insert into #tmpUserPerm (UserName, GroupName, LoginName, DefDBName, DefSchemaName, UserId, SID)
           Exec sp_helpuser @name
           fetch next from cur into @name
       END
       close cur
       deallocate cur
       drop table #Tmpuser
       select * from #tmpUserPerm order by 1
       drop table #tmpUserPerm
    END
    else --If SQL SERVER 2000 or other 
    begin
--Run cursor for all the user  names
       declare cur1 Cursor for Select name from #Tmpuser
       open cur1
       fetch next from cur1 into @name
       while @@fetch_Status = 0
       BEGIN
--Get data from sp_helpuser for current value of user (@NAME)
          insert into #tmpUserPerm (UserName, GroupName, LoginName, DefDBName, UserId, SID)
          Exec sp_helpuser @name
          fetch next from cur1 into @name
       END
       close cur1
       deallocate cur1
       drop table #Tmpuser
       select username, groupname, loginname, defdbname from #tmpUserPerm order by 1
       drop table #tmpUserPerm
    end
end

Rate

2 (3)

Share

Share

Rate

2 (3)