CODEWITHSUNDEEP
sql-serverpermissions
mordechai
mordechai

Reputation: 851

GRANT VIEW ANY DEFINITION TO PUBLIC - does not affects

I have tried to execute:

USE master
GRANT VIEW ANY DEFINITION  TO PUBLIC

but all databases are not affected from this permission.

only when I am executing 

USE SomeDB
GRANT VIEW DEFINITION TO PUBLIC

is affects, is there a limition for 'GRANT TO PUBLIC' ?

I am using 

SELECT * FROM sys.fn_my_permissions(NULL,'database')

and 

SELECT OBJECT_DEFINITION(OBJECT_ID('ObjectName'))

to determine if user has right permission

Upvotes: 2

Views: 4767

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89396

Here's a quick demo of how it's supposed to work. You grant VIEW ANY DEFNINITION to PUBLIC then any login that can connect to a database gets VIEW DEFINITION at the database level.

use master
go
grant view any definition to public
drop database permtest 
go
drop login fred
go
create database permtest
go
use permtest
go
create login fred with password = 'as@#@315lkdfjlaskfj'
create user fred for login fred 
go
create proc foo as 
begin
  select 1 a
end
go

execute as login='fred'
 SELECT concat(entity_name, ': ', permission_name) 
 FROM sys.fn_my_permissions(NULL,'database')

 SELECT OBJECT_DEFINITION(OBJECT_ID('foo'))
revert

outputs

------------------
database: CONNECT
database: VIEW ANY COLUMN ENCRYPTION KEY DEFINITION
database: VIEW ANY COLUMN MASTER KEY DEFINITION
database: VIEW DEFINITION

(4 rows affected)


-------------
create proc foo as 
begin
  select 1 a
end

(1 row affected)

And if you change grant to revoke it outputs:

------------------
database: CONNECT
database: VIEW ANY COLUMN ENCRYPTION KEY DEFINITION
database: VIEW ANY COLUMN MASTER KEY DEFINITION

(3 rows affected)


--------------
NULL

(1 row affected)

But there has to be an active login for the server-level permission to apply. It doesn't work with database-level principals like users without logins, contained database users, or when a database user is impersonated. And it doesn't grant logins access to databases they can't otherwise access.

Upvotes: 3

Related Questions