locked
Seeing database user role membership in SSMS RRS feed

  • Question

  • Can't get role memberships to show
    in SQL Server Management Studio (SSMS).

    Am running Windows XP SP3 and SQL Server
    2008 R2 Express.

    Logged in to Windows as Windows user
    Admin1 with administrator capabilities,
    via SQLCMD.EXE I execute T-SQL statements

         CREATE DATABASE ...

         CREATE TABLE ...

    and create database Search009.

    Then, to do administration of SQL Server
    needed by IIS and Visual Basic .NET
    programs using ADO.NET running on IIS, via
    SQLCMD.EXE I execute T-SQL statements:

         CREATE LOGIN [TMA-39558736\ASPNET]
         FROM WINDOWS WITH
         DEFAULT_LANGUAGE=[us_english];

         USE [Search009];
         GO
         CREATE USER [Search009_ASPNET] FOR
         LOGIN [TMA-39558736\ASPNET];

         USE [Search009];
         GO
         GRANT db_datareader, db_datawriter TO
         [Search009_ASPNET];
         GO

    Then everything looks fine in SSMS except
    I can find no evidence that Search009
    database user

         Search009_ASPNET

    is a member of database Search009 database
    roles

         db_datareader, db_datawriter

    What's wrong?
    Monday, March 7, 2011 7:49 PM

Answers

  • The permissions of the fixed roles are a different type of beast than the GRANTs. db_datareader gets something similar to GRANT SELECT ON DATABASE, but it doesn't secretly apply GRANT SELECT ON DATABASE in the background, and SELECT * FROM sys.database_permissions won't show it. Instead it uses it's own code path to establish permissions (though DENYs can still override them). This is a legacy of SQL Server 2000 and earlier, and we don't like to break old apps. So db_datareader and the others still work, but I prefer to point people to the more granular permission system (GRANT/DENY/REVOKE) introduced in SQL Server2005.

    And the reverse is also true. That is GRANT CONTROL DATABASE doesn't make someone a member of the db_owner role. So a stored procedure like sp_detach_db that checks for the db_owner role, doesn't work for users with CONTROL DATABASE permission. I've been trying to sort some of this out, but with 214 permissions in SQL Server 'Denali' it's taking me awhile.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    • Proposed as answer by WeiLin Qiao Sunday, March 13, 2011 6:46 AM
    • Marked as answer by WeiLin Qiao Tuesday, March 15, 2011 11:33 AM
    Tuesday, March 8, 2011 9:53 PM

All replies

  • Instead of GRANT, use ALTER ROLE (or the older sp_addrolemember statement) to add the user to the role. For example:

    USE [Search009];
    GO
    ALTER ROLE [db_datareader] ADD MEMBER [Search009_ASPNET]
    GO
    ALTER ROLE [db_datawriter] ADD MEMBER [Search009_ASPNET]
    GO
     


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Monday, March 7, 2011 11:03 PM
  • Super.  Many thanks.

    So, I'm guessing that my use of the T-SQL
    statement GRANT caused SQL Server (1) for
    each of the roles I listed in my statement
    GRANT, that is, for each of the roles

         db_datareader, db_datawriter

    to read the permissions that role grants
    its members and (2) then to grant those
    permissions to the database user I
    specified

         Search009_ASPNET

    And actually it does appear that this
    statement GRANT did give the user

         Search009_ASPNET

    the permissions:  I ran a little test ASPX
    Web page with Visual Basic .NET and a
    little ADO.NET usage to do a T-SQL
    statement SELECT on one of the tables in
    my database Search009, and the Web page
    did the SELECT successfully and ran fine.

    So, the user

         Search009_ASPNET

    did get at least the permission SELECT for
    the table and likely the database.

    As in

         http://technet.microsoft.com/en-us/library/ms189612(SQL.90,printer).aspx

    apparently role db_datareader grants its
    members permission SELECT, and role
    db_datawriter grants its members
    permissions DELETE, INSERT, UPDATE.

    So, apparently from my statement GRANT my
    user

         Search009_ASPNET

    got permissions SELECT, DELETE, INSERT,
    UPDATE.

    Indeed, after posting my question and
    encountering some problems I have not yet
    diagnosed, I gave up on 'roles' and just
    used

         USE [Search009];
         GO
         GRANT SELECT, DELETE, INSERT, UPDATE
         TO [Search009_ASPNET];
         GO

    which so far seems to work fine.  But
    while these T-SQL statements did let my
    test Web page access the database, I could
    not get SQL Server Management Studio (SSMS)
    to report that the permissions were
    granted to my user.

    I do have some old T-SQL scripts that
    query some system tables or views or use
    some system functions or whatever that may
    provide confirmation that the user has the
    permissions.

    In contrast, maybe each the statements
    ALTER you gave 'alters' the 'membership'
    of the role specified in the statement by
    making the user specified a 'member' of
    that role.  E.g., your

         ALTER ROLE [db_datareader] ADD MEMBER
         [Search009_ASPNET]

    would make user

         Search009_ASPNET

    a member of role db_datareader and, thus,
    grant that user permission SELECT.  Likely
    then SSMS would show this user as a member
    of this role.

    So, my statement GRANT did grant
    permissions but did not make the user a
    member of the roles.  Maybe.

    Many thanks.

    Tuesday, March 8, 2011 5:56 PM
  • The permissions of the fixed roles are a different type of beast than the GRANTs. db_datareader gets something similar to GRANT SELECT ON DATABASE, but it doesn't secretly apply GRANT SELECT ON DATABASE in the background, and SELECT * FROM sys.database_permissions won't show it. Instead it uses it's own code path to establish permissions (though DENYs can still override them). This is a legacy of SQL Server 2000 and earlier, and we don't like to break old apps. So db_datareader and the others still work, but I prefer to point people to the more granular permission system (GRANT/DENY/REVOKE) introduced in SQL Server2005.

    And the reverse is also true. That is GRANT CONTROL DATABASE doesn't make someone a member of the db_owner role. So a stored procedure like sp_detach_db that checks for the db_owner role, doesn't work for users with CONTROL DATABASE permission. I've been trying to sort some of this out, but with 214 permissions in SQL Server 'Denali' it's taking me awhile.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    • Proposed as answer by WeiLin Qiao Sunday, March 13, 2011 6:46 AM
    • Marked as answer by WeiLin Qiao Tuesday, March 15, 2011 11:33 AM
    Tuesday, March 8, 2011 9:53 PM