Technical Article

Script permissions on all databases

,

This script will generate permissions for every user in the database, and it will run on every database on the instance.

Just paste and run the script.

use master
go
begin
declare @databasename varchar(30)
declare cur cursor for 
select name from sysdatabases
create table #result
(dbname varchar(30),result varchar(300))
open cur
fetch next from cur into @databasename
while(@@fetch_status=0)
begin
create table #t 
(a1 varchar(50)
,a2 varchar(50)
,a3 varchar(50)
,a4 varchar(50)
,a5 varchar(50)
,a6 varchar(50)
,a7 varchar(50))
insert into #t exec sp_helprotect @username = null
insert into #result select @databasename,a5+' '+a6+' on ['+a1+'].['+a2+']'+
CASE
WHEN (PATINDEX('%All%', a7)=0) and (a7 <> '.') 
 THEN ' ('+a7+')'
ELSE ''
END+' to ['+a3+']' from #t
drop table #t
fetch next from cur into @databasename
end
select * from #result
close cur
deallocate cur
drop table #result
end
go

Rate

2.1 (10)

You rated this post out of 5. Change rating

Share

Share

Rate

2.1 (10)

You rated this post out of 5. Change rating