locked
User/Login in Database Instance RRS feed

  • Question

  • Hello,

    I'm able to login to a database instance using windows authentication and able to access
    any objects/any database in that database instance.

    But I do not see my user id under Security -> Logins.
    Question 1: I'm wondering how I'm ble to login to the database instance?
    Question 2: How do I verify if a user for my accound exists in the database instance or not?

    When I execute SELECT USER_NAME(), I get the result as guest.

    Thanks!

    Tuesday, April 10, 2012 9:05 PM

Answers

  • Question 1: From SSMS, how do I find out if my user is a part of Windows group? I do not see my windows user anywhere in SSMS.

    Try "SELECT * FROM sys.login_token". It seems to list all Windows groups you are a member of. No matter these groups have been granted access to SQL Server or not.

    Question 2: Also will I be able to Grant select on a particular table in a db to my user using GRANT statement? I tried and it is

    EXEC sp_addrolemember [test_READ_ROLE], [xyz\user10]

    I tried and it is giving me the error: User or role 'XYZ\User10' does not exist in this database.

    To explictly grant database rights to a specific user, you will have to add that user to the database. (In some cases, SQL Server may add the user implicitly, but I don't think this is one of them.)

    Note that in many situations, you want to work with Windows groups, but it depends a lot on your situation. (I have no idea if you are sitting at home, or in a large corporate network.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Iric Wen Wednesday, April 18, 2012 9:09 AM
    • Marked as answer by Iric Wen Monday, April 23, 2012 9:13 AM
    Wednesday, April 11, 2012 7:42 AM

All replies

  • You can have access to an SQL Server instance by membership in a Windows group. Let's say your account is DOMAIN\Fred, and you are a member of the group DOMAIN\SQL_Server_users. If this group has been granted access to SQL Server, you will also have access, even if DOMAIN\Fred on its own has not been granted access.

    There are two special groups: BUILTIN\Administrators and BUILTIN\Users. On SQL 2005 (but not later versions) BUILTIN\Administator is granted login rights at installation, which means if you are logged in as admin on the local machine, you will have access to SQL Server with sysadmin rights.

    BUILTIN\Users just refers to users on the local machine. This role is added by default to Express instances, so that any local user can access SQL Server (with no other permissions than membership in the public role).


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, April 10, 2012 9:26 PM
  • Thanks!

    Question 1: From SSMS, how do I find out if my user is a part of Windows group? I do not see my windows user anywhere in SSMS.

    Question 2: Also will I be able to Grant select on a particular table in a db to my user using GRANT statement? I tried and it is

    EXEC sp_addrolemember [test_READ_ROLE], [xyz\user10]

    I tried and it is giving me the error: User or role 'XYZ\User10' does not exist in this database.

    Tuesday, April 10, 2012 9:42 PM
  • Question 1: From SSMS, how do I find out if my user is a part of Windows group? I do not see my windows user anywhere in SSMS.

    Try "SELECT * FROM sys.login_token". It seems to list all Windows groups you are a member of. No matter these groups have been granted access to SQL Server or not.

    Question 2: Also will I be able to Grant select on a particular table in a db to my user using GRANT statement? I tried and it is

    EXEC sp_addrolemember [test_READ_ROLE], [xyz\user10]

    I tried and it is giving me the error: User or role 'XYZ\User10' does not exist in this database.

    To explictly grant database rights to a specific user, you will have to add that user to the database. (In some cases, SQL Server may add the user implicitly, but I don't think this is one of them.)

    Note that in many situations, you want to work with Windows groups, but it depends a lot on your situation. (I have no idea if you are sitting at home, or in a large corporate network.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Iric Wen Wednesday, April 18, 2012 9:09 AM
    • Marked as answer by Iric Wen Monday, April 23, 2012 9:13 AM
    Wednesday, April 11, 2012 7:42 AM