asked on

MSSQL Add Login / user to restrict access to several tables

Hi,

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.

carlsiy

I think this might point you in the right direction.

http://blogs.msdn.com/b/sqlserverfaq/archive/2010/08/31/inf-sql-security-restricting-access-to-public-on-server-database-objects-its-implications-and-ownership-chains.aspx

donhannam

ASKER

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?.

carlsiy

Public is always the default for newly created users.

DBAduck - Ben Miller

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.

donhannam

ASKER

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.

sp_MSForEachTable 'REVOKE INSERT, UPDATE, SELECT, DELETE, REFERENCES ON ? TO public'

I think this has solved my issue but be good to get confirmation my logic is correct.

ASKER CERTIFIED SOLUTION

DBAduck - Ben Miller

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DBAduck - Ben Miller

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.

http://www.dbforums.com/microsoft-sql-server/1623469-how-list-permissions-public-role-database-sql-server.html

donhannam

ASKER

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