Basic question about SQL Server permissions for users

Question

Basic question about SQL Server permissions for users

SSCrazy

Points: 2484

May 15, 2017 at 4:31 am

Hello,
I am on a bit of a learning curver and started looking at the security issues in my databases. I can see that it is not a good idea to use the db_owner permission to grant users access to their specific database, altough I inherited a third party database and inherited whatever bad comes with it - everyone is a DB_OWNER.
Based on these links:
http://sqlmag.com/sql-server/5-reasons-against-allowing-dbowner-role-permissions
https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles
we know that anyone who has DB_OWNER can potentailly kill a database/sever. Howeever there doesn't seem to be anything about actually doing it correctly. In my example I created a new user and decided to use the following permissions:

db_ddladmin	Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.
db_datawriter	Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.
db_datareader	Members of the db_datareader fixed database role can read all data from all user tables.

The user needs to create/drop his own tables, he is mapped to one specific database. Can someone post any hints on what is the best practice?

Thanks,
Richard

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Richard

You might consider a more granular security model, granting DML permissions on individual tables, EXECUTE permission on individual stored procedures, and DDL permissions at the schema level.

Does the user really need to create his own tables, though? How will the permissions for other users on those tables be managed? Is this a production database?

John

richlion2 SSCrazy Points: 2484 More actions · Answer 2

John Mitchell-245523 - Monday, May 15, 2017 5:07 AM
Richard
You might consider a more granular security model, granting DML permissions on individual tables, EXECUTE permission on individual stored procedures, and DDL permissions at the schema level.
Does the user really need to create his own tables, though? How will the permissions for other users on those tables be managed? Is this a production database?
John

Hello,
it's actually sort of a reporting database (a mini database mart) for people who pull information from other databases and then create their own data set to do their reporting. There is one person resonsible for the whole team who manages (creates/deletes tables) and knows SQL very well. Think of it as a private database area for using as BI, so being able to create objects is a key function, hence the separate database. Otherwise I as a DBA would be nagged every 5 minutes to create/delete tables and maintain them. Not feasible in my structure. Hence a more generic approach, however with the least set of permissions I can possibly give.

RandomEvent SSCrazy Points: 2963 More actions · Answer 3

Richard, why not setup a number of users for each type of access one for datareaders, one for writers etc. This breaks out the granularity and allows you to limit users to a specific function. Obviously this will depend on what is required by the users but as John said the more granular you make it the better.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

By all means, give him CREATE TABLE permission on one of the schemas in the database. But know that if you do that, he'll be dragging data in from production databases at all times of the day, which may not be what you want. If your datamart is properly designed then this shouldn't be necessary.

John

richlion2 SSCrazy Points: 2484 More actions · Answer 5

Hello,

Thank you for your answers , you are all most helpful. I'll look into giving just CREATE TABLE permissions and a more granular approach.

Kind Regards,

richlion2 SSCrazy Points: 2484 More actions · Answer 6

Hmmm, I created a new test database (being an admin). I create a new user, let me call him XXX. The only thing I see in the properies is the database mapping where I have the database role memberships, there is no way I can give any granular access. In the securables tab there is no CREATE TABLE privilege. Is there any nice document I can have that would describe the process?

Do I need to use t-sql instead of the GUI?

Would this be a good place to start?
http://sqlmag.com/database-security/using-sql-server-permissions

Thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

Actually, I think you may need ALTER permission on the schema. Make sure you understand the dangers of doing this - explained here.

John

RandomEvent SSCrazy Points: 2963 More actions · Answer 8

You'll need to GRANT the correct priviledge to the user of that database

USE AdventureWorks;
GRANT CREATE TABLE TO RichardM;
GO

This is where I originally got some of the information from https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-database-permissions-transact-sql

Just so I'm not plagiarising.

richlion2 SSCrazy Points: 2484 More actions · Answer 9

Thank you all for your answers, I'll have to dig into the subject thoroughly. Infortunately due to the fact that the power user who is creating tables creates them so often it would not be feasible for me to keep an eye on this. I would be permanently engaged in CREATE TABLE + GRANT manual executions, unless I built a tool to maintain this kind of situation in a timely manner. It was a quick-and-dirty (my managerl has been warned!!!) solution as there was a lot of pressure on our IT to give those people a separate database. At least all newcomers won't have DB_OWNER privileges any more.

Kind Regards
Richard