sqlfriends (1/21/2014)
By reading from the definition from microsoft site below, it look like we grant view definition on server level, also add the user to the database without granting any permissions is equivalent as granting the user the view definition to the securables.
No. Try this (replace ssgrep with any database you might have):
USE master
go
CREATE LOGIN bludder WITH PASSWORD = 'offentligt'
GRANT VIEW ANY DEFINITION TO bludder
go
EXECUTE AS LOGIN = 'bludder'
go
SELECT * FROM ssgrep.sys.tables
go
REVERT
go
DROP LOGIN bludder
This produces the error message
Server: Msg 916, Level 14, State 1, Line 1
The server principal "bludder" is not able to access the database "ssgrep" under the current security context.
The example also shows how you easily can test a certain permission scenario easily.
To create a user without creating a schema, use CREATE USER.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]