How to create a non-sa SQL Server login / access without control server permission

Question

I have SQL Server, and I need to give access to other users to run basic queries (just select on 1 database).

I have created a login for a user, and have amended their permissions, but once I deny access to "Control Server" that login can no longer connect.

I am totally stuck as have no experience here, but must complete this task. Please can someone help with what the next step is?

p.s. it can't be hire someone that knows what they are doing :-)

Cheers

Answer 1

You should not need to DENY CONTROL to a login. Simply do not GRANT the right to that user.

Regarding the side effects of DENY CONTROL see the post here: http://sqlity.net/en/2102/deny-control-side-effects/

Comment from that link: "The only option you have is to pay careful attention to not grant any permission to the principal that you did not indeed intend to include. You can consider using techniques like auditing to help you with this, but you cannot enforce it."

So REVOKE your DENY and just make sure that only the needed rights are granted.

Answer 2

This started as a comment the answer @RLF posted but it got a bit long so I'm putting it as an additional answer.

Two important things to remember:

1. Remember that DENY always trumps GRANT (unless you are sa or sysadmin). You can have 100 GRANTs and a single DENY will prevent the permission from being used.
2. CONTROL SERVER (or database for that matter) is a group of permissions. It's actually a fairly broad one including among other things CONNECT. You can see the details of what it covers in the poster under this link: http://social.technet.microsoft.com/wiki/contents/articles/11842.sql-server-database-engine-permission-posters.aspx (a great reference poster for permissions in general).

So if you combine those two fact as soon as you DENY CONTROL SERVER you have effectively DENYed almost every permission in the system.

This is stated in the other answers but it's important so I'm going to repeat. Only DENY if you have to. (Only GRANT if you have to too for that matter.) DENY is very useful and important but you should only use it when you need to and even then be aware of possible unintended consequences.

Answer 3

Very simply, you just need the login (Windows is best) which defaults to public server role - allows you to connect basically. Then add the user to the desired database and grant data_reader. You can get more granular if you want, but this would be the max permissions to grant a user the ability to login and run queries. You can do all this through SSMS or just run a modified version of the following:

 USE [master]
 GO
 CREATE LOGIN [domain\user] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
 GO
 USE [YourDB]
 GO
 CREATE USER [domain\user] FOR LOGIN [domain\user]
 EXEC sp_addrolemember N'db_datareader', N'domain\user'
 GO