SQL Server 2005 permissions capabilities

In SQL Server 2005, more permissions can be given at both the instance and database level, compared to previous releases. Permissions can also now be inherited. Here are some examples to illustrate this SQL Server 2005 security feature.

In SQL Server 2005, more permissions can be given both in the instance and in the database level than in previous

releases. Also, permissions can now be inherited -- for example, permissions given to a schema are inherited by the schema's objects.

Here are examples of new permissions:

  • CONTROL -- functionally equivalent to all permissions
    More on permissions in SQL Server 2005:
  • Granting permissions in SQL Server 2005
  • Managing permissions in SQL Server Reporting Services
  • granted to the object's owner and inherited by all subentities within its scope. Principals that have CONTROL permission on a securable can grant permission on that securable.
  • For example:

    -- Grant CONTROL permission on
    AdventureWorks user Michelle
    -- to user Joseph
    -- now the user Joseph can grant permissions
    on the user
    -- "Michelle":
    USE AdventureWorks;
    GRANT CONTROL ON USER::Michelle TO Joseph;

  • ALTER ANY -- provides the ability to alter properties of an object. Depending on the scope, inheritance can be limited to objects of a specific type. For example, its variation in the form ALTER ANY 'object_type' grants permissions to modify every instance of 'object_type' within server or database scope.
  • For example:

    ALTER ANY DATABASE DDL TRIGGER

    ALTER ANY SCHEMA

    ALTER ANY ROLE

  • IMPERSONATE -- permits impersonating another user, without requiring SysAdmin or dbo privileges, as was the case in SQL Server 2000.
  • For example:

    -- Grants IMPERSONATE permission on user
    Michelle to
    -- AdventureWorks application role
    Accountants.
    -- The role Accountants can now impersonate
    Michelle:
    USE AdventureWorks;
    GRANT IMPERSONATE ON USER::Michelle TO
    Accountants;

  • VIEW DEFINITION -- gives read access to an object's metadata via catalog views.
  • For example:

    -- Grant role "public" to view any object
    definition
    -- in the instance level:
    GRANT VIEW ANY DEFINITION TO public

    -- Grant role "public" to view any object
    definition
    -- in the database level:
    GRANT VIEW DEFINITION TO public

    -- Grant VIEW DEFINITION permission on
    AdventureWorks
    -- role Accountants together with GRANT
    OPTION to database
    -- user Michelle (now user Michelle can view
    the definition of the Accountants role and
    grant it permissions):
    USE AdventureWorks;
    GRANT VIEW DEFINITION ON ROLE::Accountants
    TO Michelle WITH GRANT OPTION;

    This tip is excerpted from New security model in SQL Server 2005.

    Security at the server level is typically the responsibility of the DBA or developer, rather than an organizational initiative. With this in mind, Microsoft added a number of security features to SQL Server 2005 intended to make it an easier task.

    The new security model in SQL Server 2005 defines two main security objects – principles and securables. In this edition of SQL Server Insider, expert Michelle Gutzait reviews the new security model in SQL Server 2005 and argues the importance of defined security standards and ways they should be applied, managed and controlled. Read this ezine from SearchSQLServer.com to get SQL Server security information about:

    • Security configurations for SQL Server
    • Surface area configurations
    • Data encryption
    • The strength of the new schema model and more!

    Download this issue


    ABOUT THE AUTHOR:   
    Michelle Gutzait works as a senior database consultant for ITERGY International Inc., an IT consulting firm specializing in the design, implementation, security and support of Microsoft products in the enterprise. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. For the last 10 years, she has worked exclusively with SQL Server. Her skills include SQL Server infrastructure design, database design, performance tuning, security, high availability, VLDBs, replication, T-SQL/packages coding, and more.
    Copyright 2007 TechTarget


    This was first published in October 2007

    Dig deeper on Microsoft SQL Server 2005

    Pro+

    Features

    Enjoy the benefits of Pro+ membership, learn more and join.

    0 comments

    Oldest 

    Forgot Password?

    No problem! Submit your e-mail address below. We'll send you an email containing your password.

    Your password has been sent to:

    SearchBusinessAnalytics

    SearchDataCenter

    SearchDataManagement

    SearchAWS

    SearchOracle

    SearchContentManagement

    SearchWindowsServer

    Close