sql-server - How can we identify the Objects with out permissions in SQL Server for a database

Question

We have more than 20K objects like tables, stored procedures, views, users. We have given some permissions to those objects.

Now we need to identify which objects we did not assign permissions, I can generate all objects/permissions.

Thanks

标签： sql-server

Answer 1

use tempdb
go
create table A (id int)
create table B (id int)
create table C(id int, foo int)
go
create function dbo.funA()
returns bit
as
begin
    return(0)
end
go
create view viewA
as
select * from A
go
create procedure procA
as
begin
    select 1
end
go

--select to A
grant select on A to public;
go
--deny column on C
DENY SELECT ON c(foo) to public
go

--no permissions on any part of the object
--C is not displayed, there is permission on the column
select *
from sys.objects as o
where o.is_ms_shipped = 0
and not exists(select * from sys.database_permissions as dp where dp.major_id = o.object_id)
and o.name in ('A', 'B', 'C', 'funA', 'viewA', 'procA') --just for the example

--no permissions on the object itself
select *
from sys.objects as o
where o.is_ms_shipped = 0
and not exists(select * from sys.database_permissions as dp where dp.major_id = o.object_id and dp.minor_id = 0)
and o.name in ('A', 'B', 'C', 'funA', 'viewA', 'procA')
go

drop table A
go
drop table B 
go
drop table C
go
drop function dbo.funA
go
drop view viewA
go
drop procedure procA
go