Seeing database user role membership in SSMS

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?

Answer 1

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

Answer 2

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.

Answer 3

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