How to set limited access to certain database tables?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » How to set limited access to certain database...

How to set limited access to certain database tables?

Rate Topic

Display Mode

Topic Options

Author

Message

david.ostrander

Posted Wednesday, February 13, 2013 7:47 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 09, 2013 1:28 PM
Points: 81, Visits: 624

Hello -

Developers at the company I work for want to access one of our databases to create an iPad app for entering your timecard. I have a copy of this database on another server off my production server. The database has many tables some with private company info and I only want to grant them rights to 6 tables that they can access and will help them create this app.

What is best approcah to allow them only that access to those tables?

Regards,

David

Post #1419526

opc.three

Posted Wednesday, February 13, 2013 8:18 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 6,720, Visits: 11,760

david.ostrander (2/13/2013)

Are you planning to allow the app to directly access the table? Ideally you would guide them into having the app access all data through stored procs.

For development purposes though, if you just want them to see those 6 tables you could:

1. create Server Logins for the developers on the instance (CREATE LOGIN)
2. create Database Users based on those Logins in the database (CREATE USER). By default Database Users are only a member of the public Fixed Database Role which will not have SELECT access to any tables unless someone granted additional permissions to the public Role (you may want to check).
3. create a new Database Role (CREATE ROLE) and add the Database Users you just created as members of the role (sys.sp_addrolemember)
4. grant the Database Role necessary access to those tables (GRANT INSERT, SELECT, UPDATE, DELETE ON [schemaname].[tablename] TO [RoleName];)

If you do go the route of having the app use stored procs for data access there is more you will need to do in order to allow the developers to develop procedures while still protecting your sensitive data.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1419552

david.ostrander

Posted Wednesday, February 13, 2013 2:05 PM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 09, 2013 1:28 PM
Points: 81, Visits: 624

Thank you for the response...

Would they be able to run Select statment from SSMS on any of the tables? I just tested that and they could. Is there another way to stop that as well?

Regards,
David

Post #1419743

opc.three

Posted Wednesday, February 13, 2013 2:41 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 6,720, Visits: 11,760

They will only be able to retrieve data from tables to which you grant them SELECT permissions.

If you are asking if there is a way to allow their database user to select data from a table using "the app" but not when using SSMS, no not really.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1419748

DBArrr!

Posted Thursday, February 14, 2013 12:48 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:23 PM
Points: 70, Visits: 54

You could look into using an application role for the app, but I guess even that wont prevent them from using ssms for queries.

Post #1419846

Ratheesh.K.Nair

Posted Thursday, February 14, 2013 1:31 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, May 20, 2013 9:03 AM
Points: 2,415, Visits: 3,374

Uninstall SSMS from systems they are using w00t

You can make user the windows users don't have administrator privilege and make the USER ACCOUNT CONTROL High under System and Security.

Hope this helps

Post #1419862

« Prev Topic | Next Topic »

Permissions