January 14, 2009 at 3:57 am
Hello 🙂
I am admin on a SQL server. To be honest I don't know much about that role, but I am 😛
I need to create a user that can delete, truncate and add data to tables through BULK INSERT. I also need to give the user rights to see the Stored Procedures I have created. That way he can be my back-up, but only in a simple way were he runs a script that update data as well as executing stored procedures created by me.
He should not be able to delete tables, procedures in any way.
What 'Server roles' and 'User Mappings' should I choose to create such a user?
Sorry if this is placed in the wrong forum. And thanks for your help 😉
January 14, 2009 at 4:14 am
Well, I don't think so one can enforce the said rules/permissions in a simple manner. If you want to strictly enforce those then I guess the only option left is to give permissions on individual objects (which is time consuming if you have many objects).
Otherwise, you can give db_datareader access to read data, db_datawriter to write (includes update/delete/insert), BULK INSERT requires ADMINISTRATOR BULK OPERATIONS (server permission) and individual execute permissions on procedures.
--Ramesh
January 14, 2009 at 5:20 am
What is the closest I can get? And what will the user not be able to with these permissions?
Tx btw 😉
January 14, 2009 at 5:39 am
david (1/14/2009)
What is the closest I can get? And what will the user not be able to with these permissions?Tx btw 😉
Its not about how close you can get, its about how close you want to get to.
As I said in my earlier post, "db_datareader" role will give the user read access(i.e. SELECT) on all the tables, "db_datawriter" role will give the user write access(i.e. INSERT/UPDATE/DELETE) on all the tables, TRUNCATE TABLE requires sysadmin or db_owner or table owner permissions, BULK INSERT requires INSERT & ADMINISTRATOR BULK OPERATIONS permissions (and ALTER TABLE permission, if the specific options are set in BULK INSERT statement)
--Ramesh
January 15, 2009 at 7:36 am
If all the tasks that need to be done can be set up in Stored Proceedures, I would look at granting a user permission to execute the SPs. That way they don't have any more permissions than are necessary.
Steve
January 15, 2009 at 12:56 pm
Okey that sounds as a good idea, but 2 things:
1. Can Bulk insert be executed as Stored Procedure?
2. How to set permission in the stored procedure?
January 15, 2009 at 1:34 pm
1. In the BOL section "Adding Rows Using Bulk Copy Operations" it states that "Use the BULK INSERT statement in Transact-SQL batches, stored procedures, and triggers to bulk copy data from a file into a table or view in a SQL Server database."
2. In the BOL section "Owners and Permissions" it state that "The owner of a stored procedure can grant EXECUTE permissions for the stored procedure. If the owner of a base table wants to prevent users from accessing the table directly, they can grant permissions on views or stored procedures referencing the table, but not grant any permissions on the table itself. This is the foundation of the SQL Server mechanisms to ensure that users do not see data they are not authorized to access."
Steve
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply