sp_addrolemember with Windows SQL Server Login

From: Norman (nite_eagle_at_hotmail.com)
Date: 08/21/03 

Date: 21 Aug 2003 12:29:57 -0700

At little background first...

SQL 7.0 SP3

I was assisting a developers with security on a view. He had to add
several database users to the view, so I mentioned roles to him and
provided a few of the commands. I assumed that the logins were already
defined as users in the database, so I neglected to tell him to do
that. After he applied the commands the Windows user could not access
the view. He recieved the error message that he was not a user in the
database. He is what the developer did:

A login already existed Domain1\User1.

Assuming Domain1\User1 was already a user in DB1 as User1, he followed
my instructions

In DB1
sp_addrole 'UserRole'
OK
sp_addrolemember 'UserRole' , 'User1'
This return the error, as it should since User1 was not a user in DB1
User or role 'User1' does not exist in this database.

Being the resourceful developer, he noticed that User1 was a NT Login
so he tried
sp_addrolemember 'UserRole' , 'Domain1\User1'
Which worked??? (I tried it in 2000 and it behaved as I expected,
error)

So the SQL Server login was a member of a database role. Why did this
work? The view that role UserRole had access to could not be selected.
It returned the Domain1\User1 is not a valid user in DB1 message.

If you look in the Users of DB1 via Enterprise Manager Domain1\User1
is in the list. The only odd thing is that the Database Access column
said "Via group membership" instead of permit as it normally should.
That reads like a 6.5 message.

Is this a backward compatability feature for 6.5? I can not remember
doing this or why I would by pass the database user and map a login
directly to a database role. Any comments?

Thanks,
Norman

Relevant Pages

  • Re: PDO: Switch database user without reopening connection
    ... At the bare minimum there will be a login user who only has ... modifications to the database as well (editors get update permission, ... As database connections are expensive to ... a certain visitor in the Session, and use that value to start the right ...
    (comp.lang.php)
  • Re: PDO: Switch database user without reopening connection
    ... At the bare minimum there will be a login user who only has ... the postgres user they are logged in as to one that can make ... modifications to the database as well (editors get update permission, ... As database connections are expensive to ...
    (comp.lang.php)
  • Re: SQL Developer Remote Access
    ... The developer doesn't have to have access to SQL Server at all in order to develop the application. ... He can develop locally at their location and when they are ready to deploy they create the database part of the system at your location. ... You just created a> Login ...
    (microsoft.public.windows.server.sbs)
  • RE: How to allow users to change their password?
    ... be set up to provide the Security dialog window for password changes. ... I'll have to login using their login ... > name/password first. ... See http://www.QBuilt.com for all your database needs. ...
    (microsoft.public.access.security)
  • Re: Please! Doesnt anyone know a better way to do this?
    ... account, they need to automatically be directed to the page to enter data ... session variable on the Account page. ... I assume here that you're checking a database when the user attempts to ... When a new user attempts to login or clicks to register, ...
    (microsoft.public.dotnet.framework.aspnet)