MSSQL Add Login / user to restrict access to several tables

Posted on 2013-10-14
Medium Priority
Last Modified: 2013-10-14

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.
Question by:donhannam
  • 3
  • 3
  • 2

Author Comment

ID: 39572542
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?.

Expert Comment

ID: 39572583
Public is always the default for newly created users.
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 39572644
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.

Author Comment

ID: 39572709
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.
LVL 25

Accepted Solution

DBAduck - Ben Miller earned 2000 total points
ID: 39572719
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.
LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 39572728
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.


Author Closing Comment

ID: 39572740
Thanks for confirmation - I've tested a couple of ways and permissions are all OK now.

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question