add a login to a database role (was "Very Confusing")

This stuff makes no sense what so ever,

In the Books on Line of MSSQL2000
In "Adding a Member to a SQL Server Database Role"

There is this
"Note : When you add a Windows NT 4.0 or Windows 2000 login without a user account in the database to a SQL Server database role, SQL Server creates a user account in the database automatically, even if that Windows NT 4.0 or Windows 2000 login cannot otherwise access the database."

I mean how can anyone add a login to a database role without making the login a user of the database.:shocked:
Also if it worked, a new fancy feature to add new logins??:eek:
Plz help:SI believe it adds it as a user automatically.|||I believe it adds it as a user automatically.

But i cannot do it:eek:
"user" must exist before "role".
If you can do it,plz explain:)|||The following discussion assumes that BUILTIN\Administrators is part of the SQL Server sysadmin group:

1. Create a junk Windows login
2. Make that login a member of the machine's Administrators group
3. Start SQL Enterprise Mangler
4. Select the target database
5. Create a new (junk) role within the database.
6. Verify there is no user in the db for your junk login
7. Add your junk Windows login to your junk role.
8. Verify there is now a user in the db for your junk login

The reason that you could add the login to the role was because they implicitly have access to the database because they are members of the sysadmin role. Once you assign them to a role within the database, SQL Server needs some way to store that membership information, so it has to create a user where none explicitly existed before.

-PatP|||7. Add your junk Windows login to your junk role.
-PatP

1&2>created a user account with account type computer administrator.
3>started enterprise manager
4>selected a target db
5>created junk dbrole
6>verified no junk user
7>double clicked junk dbrole, clicked add..
:confused: junk user not there:confused:
Only users who are database "users" are appearing.

I am working on an XP,maybe thats why the difference.|||So you can see the junk login inside the junk group, but you can not see the junk login in the database users ?!?!

-PatP|||No, the junk login does not appear when i click the add button of the junk role.

The junklogin will appear only if i give junklogin access to the SQL server first and then make the junklogin a user of the database first.
Only after this is done, can i see the junklogin, infact any login works this way.|||I'm one of the "scripts our us" kind of guys... Rather than asking you to do 10,000 clicks, can you run:DECLARE @.cJunque sysname
SELECT @.cJunque = 'MyMachine\JunqueLogin' -- Machine Administrator

EXECUTE sp_helpuser @.cJunque -- Prove "no one home"

EXECUTE sp_addrole 'Junque' -- Create group
EXECUTE sp_helprole 'Junque' -- Confirm still empty

EXECUTE sp_addrolemember 'Junque', @.cJunque -- Add junk login

EXECUTE sp_helprole 'Junque' -- See who's "home" now
EXECUTE sp_helpuser @.cJunque -- Should be a user

EXECUTE sp_droprole 'Junque' -- Tidy up...
EXECUTE sp_dropuser @.cJunque -- ...completely...and post the output?

-PatP|||That worked :D
It even says in Enterprise Manager, "Database Access : Via group membership" for junkuser.
Only i think the user has to be droped before the group.

I dont think this can be done through Enterprise Manager directly??

:beer: Thanks.:beer:|||Server: Msg 15198, Level 16, State 1, Procedure sp_helpuser, Line 189
The name supplied (piii\junkuser) is not a user, role, or aliased login.
New role added.

(1 row(s) affected)

'piii\dbtemp' added to role 'Junque'.

(1 row(s) affected)

Server: Msg 15198, Level 16, State 1, Procedure sp_helpuser, Line 189
The name supplied (piii\junkuser) is not a user, role, or aliased login.
Server: Msg 15144, Level 16, State 1, Procedure sp_droprole, Line 53
The role has members. It must be empty before it can be dropped.

User has been dropped from current database.