Need a quick&dirty script to show permissions 1

[JayKusch]

As the title says ... "Need a quick&dirty script to show permissions" on objects like stored procedures, views ect.

Thanks!

Thanks

J. Kusch

 

[JayKusch]

Think I found it ... Thanks ALL!

set nocount on
select '--OBJECT LEVEL PERMISSIONS'
select 'GRANT SELECT ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 1 & syspermissions.actadd > 0
go
select 'DENY SELECT ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 1 & syspermissions.actmod > 0
go
select 'GRANT UPDATE ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 2 & syspermissions.actadd > 0
go
select 'DENY UPDATE ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 2 & syspermissions.actmod > 0
go
select 'GRANT REFERENCES ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 4 & syspermissions.actadd > 0
go
select 'DENY REFERENCES ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 4 & syspermissions.actmod > 0
go
select 'GRANT INSERT ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 8 & syspermissions.actadd > 0
go
select 'DENY INSERT ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 8 & syspermissions.actmod > 0
go
select 'GRANT DELETE ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 16 & syspermissions.actadd > 0
go
select 'DENY DELETE ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 16 & syspermissions.actmod > 0
go
select 'GRANT EXECUTE ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 32 & syspermissions.actadd > 0
go
select 'DENY EXECUTE ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 32 & syspermissions.actmod > 0
go

Thanks

J. Kusch