Post reply

List all users - Urgent

  • August 14, 2006 at 11:54 am

    #61077

    Hi,

    Does anyone have a SP or a script to find all the users, with access rights to different databases within a SQL Server.

    Thank you.

    -R

  • August 14, 2006 at 12:30 pm

    #654923

    It's crude, but it serves the purpose for which it's intended. It shows users, databases and roles for each user, and permissions granted directly to each user.

    It DOES NOT show the specific permissions granted to the roles that a user belongs to, nor does it show permissions granted to public or guest.

    declare @name sysname, @sid nvarchar(4000), @qry varchar(8000)
declare crs cursor local for 
select name, master.dbo.fn_varbintohexstr(sid) from master.dbo.syslogins --where name = 'fred'
order by name
open crs
fetch next from crs into @name, @sid
while @@fetch_status = 0
begin
  print ''; print ''
  print replicate('=',230)
  print '== User: '+@name
  print replicate('=',230)
  print ''
  exec sp_helplogins @name
  set @qry =  'if exists(select 1 from [?].dbo.sysusers where master.dbo.fn_varbintohexstr(sid) = '''+@sid+''') '+
              'begin '+
                'declare @n sysname, @u int, @m varchar(100); '+
                'select @u = uid, @n = name from [?].dbo.sysusers where master.dbo.fn_varbintohexstr(sid) = '''+@sid+'''; '+
                'if exists(select 1 from [?].dbo.sysprotects where uid = @u) '+
                'begin '+
                  'set @m = ''** Database: [?] **''; print replicate(''*'',len(@m)); print @m; print replicate(''*'',len(@m)); '+
                  'exec [?].dbo.sp_helprotect @username=@n; '+
                'end '+
              'end'
  exec sp_msforeachdb @qry
  fetch next from crs into @name, @sid
end
close crs
deallocate crs

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply