December 16, 2009 at 3:41 am
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
December 16, 2009 at 4:30 am
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.
December 16, 2009 at 4:42 am
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
December 20, 2009 at 9:01 am
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]
January 3, 2010 at 11:32 pm
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
April 26, 2016 at 10:09 am
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply