Please use FireFox to view this page

CREATE PROCEDURE sp_helpuser --- 1996/08/14 10:33 @name_in_db sysname = NULL --User,Group,Alias AS Set nocount on Set ansi_warnings off Declare @RetCode int ,@_rowcount int Declare @charMaxLen_UsName varchar(11) ,@charMaxLen_GrName varchar(11) ,@charMaxLen_LoName varchar(11) ,@charMaxLen_DbName varchar(11) Declare @Name1Type char(2) ,@CMaxUsUID smallint ----------------------- create holding table -------------------- /*Create temp table before any DML to ensure dynamic*/ Create Table #tb1_uga ( zUserName sysname collate database_default Null ,zGroupName sysname collate database_default Null ,zLoginName sysname collate database_default Null ,zDefDBName sysname collate database_default Null ,zUID smallint Null ,zSID varbinary(85) Null ) -------- Select @RetCode = 0 ,@Name1Type = Null ,@CMaxUsUID = 16383 ------------- What type of value (U,G,A) was input? -------------- -------- NULL IF (@name_in_db IS Null) begin Select @Name1Type = '-' INSERT into #tb1_uga ( zUserName ,zGroupName ,zLoginName ,zDefDBName ,zUID ,zSID ) SELECT usu.name ,case when (usg.uid is null) then 'public' else usg.name end ,lo.loginname ,lo.dbname ,usu.uid ,usu.sid from sysusers usu left outer join (sysmembers mem inner join sysusers usg on mem.groupuid = usg.uid) on usu.uid = mem.memberuid left outer join master.dbo.syslogins lo on usu.sid = lo.sid where (usu.islogin = 1 and usu.isaliased = 0 and usu.hasdbaccess = 1) and (usg.issqlrole = 1 or usg.uid is null) GOTO LABEL_25NAME1TYPEKNOWN end -------- USER INSERT into #tb1_uga ( zUserName ,zGroupName ,zLoginName ,zDefDBName ,zUID ,zSID ) SELECT usu.name ,case when (usg.uid is null) then 'public' else usg.name end ,lo.loginname ,lo.dbname ,usu.uid ,usu.sid from sysusers usu left outer join (sysmembers mem inner join sysusers usg on mem.groupuid = usg.uid) on usu.uid = mem.memberuid left outer join master.dbo.syslogins lo on usu.sid = lo.sid where (usu.islogin = 1 and usu.isaliased = 0 and usu.hasdbaccess = 1) and (usg.issqlrole = 1 or usg.uid is null) and usu.name = @name_in_db Select @_rowcount = @@rowcount IF (@_rowcount > 0) begin Select @Name1Type = 'US' GOTO LABEL_25NAME1TYPEKNOWN end -------- ALIAS INSERT into #tb1_uga ( zUserName ,zGroupName ,zLoginName ,zDefDBName ,zUID ,zSID ) SELECT usu.name ,case when (usg.uid is null) then 'public' else usg.name end ,lo.loginname ,lo.dbname ,usu.uid ,usu.sid from (SELECT sid, altuid FROM sysusers WHERE isaliased = 1) al inner join (sysusers usu left outer join (sysmembers mem inner join sysusers usg on mem.groupuid = usg.uid) on usu.uid = mem.memberuid left outer join master.dbo.syslogins lo on usu.sid = lo.sid) on al.altuid = usu.uid where (usu.islogin = 1 and usu.isaliased = 0) and (usg.issqlrole = 1 or usg.uid is null) and al.sid = suser_sid(@name_in_db) Select @_rowcount = @@rowcount IF (@_rowcount > 0) begin Select @Name1Type = 'AL' GOTO LABEL_25NAME1TYPEKNOWN end -------- GROUP IF EXISTS (SELECT * FROM sysusers WHERE name = @name_in_db AND (issqlrole = 1) ) begin Select @Name1Type = 'GR' Execute sp_helpgroup @name_in_db GOTO LABEL_75FINAL --Done end -------- Error Raiserror(15198,-1,-1 ,@name_in_db) --Input Name is unfound Select @RetCode = @RetCode | 1 GOTO LABEL_75FINAL -------- LABEL_25NAME1TYPEKNOWN: ----------------------- Printout the report ------------------------- -------- Preparations for dynamic exec SELECT @charMaxLen_UsName = convert( varchar, isnull( max( datalength( zUserName)),8)) ,@charMaxLen_GrName = convert( varchar, isnull( max( datalength( zGroupName)),9)) ,@charMaxLen_LoName = convert( varchar, isnull( max( datalength( zLoginName)),9)) ,@charMaxLen_DbName = convert( varchar, isnull( max( datalength( zDefDBName)),9)) from #tb1_uga -------- Dynamic EXEC() to printout report EXECUTE( ' SELECT ''UserName'' = substring(zUserName ,1,' + @charMaxLen_UsName + ') ,''GroupName'' = substring(zGroupName,1,' + @charMaxLen_GrName + ') ,''LoginName'' = substring(zLoginName,1,' + @charMaxLen_LoName + ') ,''DefDBName'' = substring(zDefDBName,1,' + @charMaxLen_DbName + ') ,''UserID'' = convert(char(6),zUID) ,''SID'' = zSID from #tb1_uga order by 1 ' ) ----------------------- A little extra nice-to-have IF (@Name1Type IN ('-','US')) begin IF EXISTS (SELECT * FROM #tb1_uga tb1 ,(SELECT altuid FROM sysusers WHERE isaliased = 1) al, sysusers us WHERE tb1.zUID = us.uid and us.uid = al.altuid ) begin SELECT 'LoginName' = suser_sname(al.sid) ,'UserNameAliasedTo' = tb1.zUserName from #tb1_uga tb1 ,(SELECT sid, altuid FROM sysusers WHERE isaliased = 1) al, sysusers us WHERE tb1.zUID = us.uid and us.uid = al.altuid order by 1 end end ----------------------- Finalization ---------------------- LABEL_75FINAL: IF (object_id('tempdb..#tb1_uga') IS not Null) Drop Table #tb1_uga return (0) -- sp_helpuser