locked
Database Users showing disabled RRS feed

  • Question

  • In SSMS, a database user is showing up as disabled (a down reddish arrow). When I look at the server login - it shows that it is a valid login to the instance. I go to Login Properties and then click User Mapping on the GUI and check the particular database to add/enable this user to a Role. However, no luck. It still shows the user as disabled. The user cannot connect to the database. This is in SQL Server 2005, EE64.

    Can someone please help? - Thanks.

    Tuesday, April 5, 2011 3:29 PM

Answers

  • In SSMS, a database user is showing up as disabled (a down reddish arrow). When I look at the server login - it shows that it is a valid login to the instance. I go to Login Properties and then click User Mapping on the GUI and check the particular database to add/enable this user to a Role. However, no luck. It still shows the user as disabled. The user cannot connect to the database. This is in SQL Server 2005, EE64.

    Can someone please help? - Thanks.


    Starting from SQL server 2005 , one way to disable a user in a database is to run a REVOKE CONNECT FROM permission for that user . If that is the case , you can run a GRANT CONNECT TO and fix this problem.
    Thanks, Leks
    • Marked as answer by DbsBasu Wednesday, April 6, 2011 5:03 PM
    Tuesday, April 5, 2011 11:47 PM
  • I believe database user is also disabled. You can use either GUI to enable it or use following TSQL to enable the database user:

    use

    [dbName]
    GO
    GRANT
    CONNECT TO [UserName]
    GO

    • Proposed as answer by Sunil Gure Wednesday, April 6, 2011 8:07 AM
    • Marked as answer by DbsBasu Wednesday, April 6, 2011 5:06 PM
    Wednesday, April 6, 2011 8:04 AM
  • Just to clarify:

    There is no "disable" property for a database user. The red arrow measn that somebody revoked connect permissions to the database for the user. I.e., no use looking fomr some "disable" option in the GUI or at the T-SQL command level - GRANT CONNECT is what is needed.

    Also, a "disabled" user (revoked connect) does in no way neccesarily mean that the corresponding login (if such exist) was also disabled (or DENY). Different settings, at different levels. The red arrow for the user only look at the user status, it does not look at the corresponding login status.


    Tibor Karaszi, SQL Server MVP | web | blog
    • Marked as answer by DbsBasu Wednesday, April 6, 2011 5:03 PM
    Wednesday, April 6, 2011 12:34 PM
  • I believe that DbsBasu is talking about the user, not login. A login and be Disabled or Deny, but there not such setting for a user. What does exist for a user, though is that you can remove connect permissions (just as Leks stated), which will show up as a red arrow in the GUI.
    Tibor Karaszi, SQL Server MVP | web | blog
    • Marked as answer by DbsBasu Wednesday, April 6, 2011 5:07 PM
    Wednesday, April 6, 2011 7:10 AM
  • In SSMS, a database user is showing up as disabled (a down reddish arrow). When I look at the server login - it shows that it is a valid login to the instance. I go to Login Properties and then click User Mapping on the GUI and check the particular database to add/enable this user to a Role. However, no luck. It still shows the user as disabled. The user cannot connect to the database. This is in SQL Server 2005, EE64.

    Can someone please help? - Thanks.


    Starting from SQL server 2005 , one way to disable a user in a database is to run a REVOKE CONNECT FROM permission for that user . If that is the case , you can run a GRANT CONNECT TO and fix this problem.
    Thanks, Leks

    Thanking everyone for answering. I went to the database and granted connect. There were some 50+ users on two databases. So I wrote a quick looping script to grant connect all. Thanks again.
    • Marked as answer by DbsBasu Wednesday, April 6, 2011 5:07 PM
    Wednesday, April 6, 2011 5:06 PM

All replies

  • Is this database restored from different server? try if sp_change_users_login can solve the problem check here for more http://msdn.microsoft.com/en-us/library/aa259633(v=sql.80).aspx


    http://uk.linkedin.com/in/ramjaddu
    Tuesday, April 5, 2011 7:15 PM
  • In SSMS, a database user is showing up as disabled (a down reddish arrow). When I look at the server login - it shows that it is a valid login to the instance. I go to Login Properties and then click User Mapping on the GUI and check the particular database to add/enable this user to a Role. However, no luck. It still shows the user as disabled. The user cannot connect to the database. This is in SQL Server 2005, EE64.

    Can someone please help? - Thanks.


    Starting from SQL server 2005 , one way to disable a user in a database is to run a REVOKE CONNECT FROM permission for that user . If that is the case , you can run a GRANT CONNECT TO and fix this problem.
    Thanks, Leks
    • Marked as answer by DbsBasu Wednesday, April 6, 2011 5:03 PM
    Tuesday, April 5, 2011 11:47 PM
  • Go to the login properties, check the status of the login and from there you can enable the login.

    Thanks,

    Kalpana Pathak

    Wednesday, April 6, 2011 4:56 AM
  • I believe that DbsBasu is talking about the user, not login. A login and be Disabled or Deny, but there not such setting for a user. What does exist for a user, though is that you can remove connect permissions (just as Leks stated), which will show up as a red arrow in the GUI.
    Tibor Karaszi, SQL Server MVP | web | blog
    • Marked as answer by DbsBasu Wednesday, April 6, 2011 5:07 PM
    Wednesday, April 6, 2011 7:10 AM
  • I believe database user is also disabled. You can use either GUI to enable it or use following TSQL to enable the database user:

    use

    [dbName]
    GO
    GRANT
    CONNECT TO [UserName]
    GO

    • Proposed as answer by Sunil Gure Wednesday, April 6, 2011 8:07 AM
    • Marked as answer by DbsBasu Wednesday, April 6, 2011 5:06 PM
    Wednesday, April 6, 2011 8:04 AM
  • Just to clarify:

    There is no "disable" property for a database user. The red arrow measn that somebody revoked connect permissions to the database for the user. I.e., no use looking fomr some "disable" option in the GUI or at the T-SQL command level - GRANT CONNECT is what is needed.

    Also, a "disabled" user (revoked connect) does in no way neccesarily mean that the corresponding login (if such exist) was also disabled (or DENY). Different settings, at different levels. The red arrow for the user only look at the user status, it does not look at the corresponding login status.


    Tibor Karaszi, SQL Server MVP | web | blog
    • Marked as answer by DbsBasu Wednesday, April 6, 2011 5:03 PM
    Wednesday, April 6, 2011 12:34 PM
  • In SSMS, a database user is showing up as disabled (a down reddish arrow). When I look at the server login - it shows that it is a valid login to the instance. I go to Login Properties and then click User Mapping on the GUI and check the particular database to add/enable this user to a Role. However, no luck. It still shows the user as disabled. The user cannot connect to the database. This is in SQL Server 2005, EE64.

    Can someone please help? - Thanks.


    Starting from SQL server 2005 , one way to disable a user in a database is to run a REVOKE CONNECT FROM permission for that user . If that is the case , you can run a GRANT CONNECT TO and fix this problem.
    Thanks, Leks

    Thanking everyone for answering. I went to the database and granted connect. There were some 50+ users on two databases. So I wrote a quick looping script to grant connect all. Thanks again.
    • Marked as answer by DbsBasu Wednesday, April 6, 2011 5:07 PM
    Wednesday, April 6, 2011 5:06 PM
  • Tibor:

    I'm currently trying to understand this scenario in SQL Server 2012.

    Imagine a "disabled" user (revoked connect) is a Windows-based individual user with permissions granted to objects within the database.  In Active Directory this user is a member of Windows-based groups that are valid database users with connect permission to the database.  The user connects to the database successfully, as a recognized member of several Windows-based groups that are valid users.  Each of the groups in which they are a member have permissions to various database objects, but the individual user (revoked connect) also has object and schema permissions. 

    Once connected to the database, will the user's individual permissions be effective?

    Is there any official documentation showing this type of scenario and how it works? 

    Thank you,

    Renee

    Friday, February 9, 2018 4:34 PM
  • I don't know of any documentation for this. Possibly, and the product documentation would be the first place to see if it covers this scenario.

    How it works? It would be pretty easy to test, so I leave that to you. (Let us know what you find.) 

    But I have to venture a guess: My guess is that the person will have the privileges granted for the groups logins' that the person belongs to. And not the privileges granted to the individual logins' user.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Saturday, February 10, 2018 5:28 PM
  • Thanks Sunil

    This helped me 

    Thursday, September 19, 2019 4:44 PM