Home > SQL Server Tips > Database Administration > SQL Server 2005 permissions capabilities
SQL Server Tips:
EMAIL THIS
 TIPS & NEWSLETTERS TOPICS 

DATABASE ADMINISTRATION

SQL Server 2005 permissions capabilities


Michelle Gutzait
10.08.2007
Rating: --- (out of 5)


Expert advice on database administration
Digg This!    StumbleUpon Toolbar StumbleUpon    Bookmark with Delicious Del.icio.us    Add to Google


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 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 Serve

    Digg This!    StumbleUpon Toolbar StumbleUpon    Bookmark with Delicious Del.icio.us    Add to Google


    RELATED CONTENT
    SQL Server Security
    Setting up SQL Server Service Broker for secure communication
    The keys to database backup protection for SQL Server
    Understanding transparent data encryption in SQL Server 2008
    The fine line between not encrypting your databases and breach notification
    Securing SQL Server with access control, login monitoring and DDL triggers
    SQL Server security: Controlling access via database roles
    Implementing security audit in SQL Server 2008
    New security features in SQL Server 2008 leave some work for you
    Can I encrypt and restore a database backup in SQL Server 2005?
    FAQ: How to troubleshoot and grant SQL Server permissions

    Microsoft SQL Server 2005 (Yukon)
    SQL Server Reporting Services Fast Guide
    SQL Server Service Broker Tutorial and Reference Guide
    Tips for tuning SQL Server 2005 to improve reporting performance
    SQL Server consolidation: Why it's an optimization technique
    Parent-child dimensions in SQL Server 2005 with Analysis Services MDX
    Enforcing data integrity in a SQL Server database
    SSIS error message due to installation problem on SQL Server 2005
    Should you upgrade to SQL Server 2005 or SQL Server 2008?
    Basics for working with DATETIME and SMALLDATETIME in SQL Server 2005
    How to configure Database Mail in SQL Server 2005 to send mail
    Microsoft SQL Server 2005 (Yukon) Research

    Database Administration
    Setting up SQL Server Service Broker for secure communication
    Top load balancing methods for SQL Server
    Performance implications of transaction log autogrowth in SQL Server
    The keys to database backup protection for SQL Server
    Understanding transparent data encryption in SQL Server 2008
    Working with sparse columns in SQL Server 2008
    Determining the source of full transaction logs in SQL Server
    Implementing SQL Server 2008 FILESTREAM functionality
    Improving SQL Server full-text search performance
    Using the OPENROWSET function in SQL Server

    RELATED GLOSSARY TERMS
    Terms from Whatis.com − the technology online dictionary
    data corruption  (SearchSQLServer.com)
    data hiding  (SearchSQLServer.com)

    RELATED RESOURCES
    2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems
    Search Bitpipe.com for the latest white papers and business webcasts
    Whatis.com, the online computer dictionary


    r 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.

    [IMAGE]

    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


    [TABLE]


    Rate this Tip
    To rate tips, you must be a member of SearchSQLServer.com.
    Register now to start rating these tips. Log in if you are already a member.




    DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.



    SQL Server Development - .NET, C#, T-SQL, Visual Basic
    HomeNewsTopicsITKnowledge ExchangeTipsAsk the ExpertsMultimediaWhite PapersIT Downloads
    About Us  |  Contact Us  |  For Advertisers  |  For Business Partners  |  Site Index  |  RSS
    SEARCH 
    TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of technology-specific websites, events and online magazines.

    TechTarget Corporate Web Site  |  Media Kits  |  Site Map




    All Rights Reserved, Copyright 2005 - 2009, TechTarget | Read our Privacy Policy     		  TechTarget - The IT Media ROI Experts