MSSQL Add Login / user to restrict access to several tables


I wanted to set up a Login to MSSQL that enabled access to only a few tables.
I have tried this in management studio:
I can create a new Login but this defaults to the Public role which I cannot untick.
I can then set up a database role and search for the tables to allow access to.

However the only way I seem to be able to deny access is to select all tables. Even if I do this I am concerned any new tables created will default to allow access as well as stored procedures etc.

Is there a way I can set up a new Login without access to any tables by default and then define the ones required - happy to do in TSQL if easier.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

donhannamAuthor Commented:
Thanks - I had a look at this and seemed to say it wasn't possible?. Looks like public will apply to all logins - it may be that I can change public but I would prefer not to.

Hoping someone has found a way to set up a new Login without needing to inherit public?.
Public is always the default for newly created users.
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

DBAduck - Ben MillerPrincipal ConsultantCommented:
By default public does not have access to anything anyway, so that should not be a concern.  You should not by default grant any permissions to public.  The default permission to the server role public is to CONNECT on all endpoints except Dedicated Admin.

So just don't grant any permissions to [public] and don't revoke permissions.  Create your own role and grant permissions to that role and add the user to that role.
donhannamAuthor Commented:
Ok thanks for that - The database I am using has most of the tables set up with public permissions set to allow update, delete and select.

I noticed some of the tables created later don't have this and are not available from the new login I set up.

I tested functionality and all apps work fine without public permissions ticked.

I found that running 'REVOKE INSERT, UPDATE, SELECT, DELETE, REFERENCES ON Customer TO public' cleared this with TSQL rather than having to untick each table.

I looked up a utility called sp_MSForEachTable to run for each table which worked well.


I think this has solved my issue but be good to get confirmation my logic is correct.
DBAduck - Ben MillerPrincipal ConsultantCommented:
Yes, that is what you would do is REVOKE permissions that you don't want on there.  Permissions are not inheritable unless you use a db role like db_datareader or db_datawriter and then whatever is added they get.  So stay clear of those.

but otherwise they have to be explicitly granted, so revoking them from public and putting them in the proper role, you will clear yourself of unwanted badness when adding another user since public is the default role.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DBAduck - Ben MillerPrincipal ConsultantCommented:
You can also use a script like the one on this page if you ever wonder what Public has. Just change to the database and execute the script in this post.
donhannamAuthor Commented:
Thanks for confirmation - I've tested a couple of ways and permissions are all OK now.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.