Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2006
    Location
    System.Web.UI.WebControls.Home.BedRoom2
    Posts
    42

    Lightbulb Unanswered: 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.
    Also if it worked, a new fancy feature to add new logins??
    Plz help

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I believe it adds it as a user automatically.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2006
    Location
    System.Web.UI.WebControls.Home.BedRoom2
    Posts
    42

    Lightbulb

    Quote Originally Posted by blindman
    I believe it adds it as a user automatically.
    But i cannot do it
    "user" must exist before "role".
    If you can do it,plz explain

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  5. #5
    Join Date
    Apr 2006
    Location
    System.Web.UI.WebControls.Home.BedRoom2
    Posts
    42

    Lightbulb

    Quote Originally Posted by Pat Phelan
    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..
    junk user not there
    Only users who are database "users" are appearing.

    I am working on an XP,maybe thats why the difference.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    So you can see the junk login inside the junk group, but you can not see the junk login in the database users ?!?!

    -PatP

  7. #7
    Join Date
    Apr 2006
    Location
    System.Web.UI.WebControls.Home.BedRoom2
    Posts
    42
    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.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm one of the "scripts our us" kind of guys... Rather than asking you to do 10,000 clicks, can you run:
    Code:
    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

  9. #9
    Join Date
    Apr 2006
    Location
    System.Web.UI.WebControls.Home.BedRoom2
    Posts
    42

    Lightbulb

    That worked
    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??

    Thanks.

  10. #10
    Join Date
    Apr 2006
    Location
    System.Web.UI.WebControls.Home.BedRoom2
    Posts
    42

    Lightbulb Output

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •