What does GRANT CONNECT exactly do?

  • Anders Hansen

    Ten Centuries

    Points: 1170

    Hi

    I have just accidently happened to revoke the CONNECT right to some users on my test database.

    But it seems as if the sites using the login still works, even though the CONNECT right isn't granted.

    So that made me wonder what the CONNECT right actually does?

    I have the situation for both windows logins and a SQL login. Only dbo is granted CONNECT to the database.

    Can somebody help clarify what the CONNECT right is giving?

    Best regards

    /Anders

  • ss-457805

    SSCertifiable

    Points: 5872

    Grant Connect Grants the user permissions to connect to SQL Server. Do the users belong to a group and the group has got Connect permissions.

  • Anders Hansen

    Ten Centuries

    Points: 1170

    The SQL login user e.g., does belong to a role, but nothing but dbo has CONNECT right to the database.

    Has tested a little further, and found that the user can use the stored procedures it has execute rights, but it can't connect to a database using Management Studio.

    So if I try to connect through Management Studio, and browse my database I get an error.

    But if I just open a New Query, I can do stuff like "USE myDatabase", "EXEC MyProc", "SELECT * FROM dbo.MyFunction", etc...

    So I can perform all the things I have execute/select rights to, just not Connect and browse using the Management Studio.

    This leaves me to think that I actually would prefer that my users don't have CONNECT rights on the live servers either.

    But I would like to hear from someone who knows explicitly how this works, before I revoke the CONNECT option.

    /Anders

  • RBarryYoung

    SSC Guru

    Points: 143327

    No something is wrong here. Without the CONNECT right, a Login cannot use any resources in the database. If they are getting in "some other way" then they are getting the CONNECT permission some other way (such as through sa, sysadmin role, or something else that is allowing them to assume the dbo's rights). Or else you are actually connecting to a different server with the same database names.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Anders Hansen

    Ten Centuries

    Points: 1170

    Ok.

    I will go back and verify the rights of the users.

    What would be the most correct way to figure out who has the connect right?

    I would do a select from sys.database_permissions, and join that with sys.database_principals.

    Is that enough or can there be some inherited Connect rights from server level or other places?

    It's possibly just me missing something here, since you both agree that I shouldn't be able to do anything in the database without the connect right.

    /Anders

  • Jamie-2229

    SSCrazy Eights

    Points: 8151

    CONNECT SQL is a right given to the user through the public role. (Database level - Security | Roles | Database Roles | public

    The CONNECT permission can be removed from the public role. If it is removed, then it must be added somewhere else or your users won't connect. If someone knows that process, feel free to comment and discuss. Public role is fickle IMHO.

    Yep, six years old - still, as permissions get tighter, this isssue becomes more important over time.

    Jamie

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply