Implementing New Manageability Features and Practices - Microsoft SQL Server 2008 R2 Administration - page 271

4. The value (DEFAULT) will return all the parent class permissions regardless of

securable scope. The various parent classes (essential audit perspective) are
SERVER , DATABASE , and SCHEMA . Also, the server objects such as Endpoints, Event
Notifications, or Linked Servers are represented with a NULL value.

5. To manage the securable, we need to use Data Control Language (DCL) to create

permissions used to control access to the database by securing it. The syntax is
as follows:
GRANT { ALL [ PRIVILEGES ] }
| permission [ (column [ ,...n ] ) ] [ ,...n ]
[ ON [ class:: ] securable ] TO principal [ ,...n ]
[ WITH GRANT OPTION ] [ AS principal ]

6. The following TSQL can be used to manage server-scoped securables and set

database-level permissions to a principal on a securable:
-- Create recipe new login
IF NOT EXISTS
(SELECT name
FROM sys.server_principals WHERE name = 'DBIAUser')
BEGIN

CREATE LOGIN [DBIAUser]

WITH PASSWORD=N'$ql3rver',

DEFAULT_DATABASE=[master],

CHECK_EXPIRATION=OFF,

CHECK_POLICY=ON

END
--User will have permission to GRANT permission to other grantees
GRANT VIEW ANY DATABASE TO [DBIAUser] WITH GRANT OPTION

--GRANT additonal securable
GRANT CREATE ANY DATABASE TO [DBIAUser]

--DENY permission to a securable
DENY SHUTDOWN TO [DBIAUser]

--REVOKE permission on a server securabe
REVOKE ALTER TRACE FROM [DBIAUser] CASCADE

7. Obtain information on the server-scoped permission for the new login created earlier:

--Query server-level permissions
SELECT p.class_desc, p.permission_name, p.state_desc
FROM sys.server_permissions p
INNER JOIN sys.server_principals s ON p.grantee_principal_id =
s.principal_id
WHERE s.name = 'DBIAUser'