Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Thursday, September 6, 2012 10:06 AM
Hi,
Can anyone make any recommendations as to what is the best approach to give a user permissions for DML operations on a database and only the ability to CREATE, ALTER, DROP stored procedures? The stored procedures can only perform DML operations.
The preferred way would be to create a database role and give the role the DML and GRANT CREATE PROCEDURE TO permissions. However, testing has proven that this will not meet the requirements.
And from the manual:
Requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created, or requires membership in the db_ddladmin fixed database role.
Without ALTER SCHEMA:
Msg 2760, Level 16, State 1, Procedure BackupInfo, Line 9
The specified schema name "dbo" either does not exist or you do not have permission to use it.
I also understand there are other solutions:
-
- Create a DDL trigger that only allows stored procedure DDL actions
- Create a separate schema where developers have full permissions
However, from our security perspective role based permissions in theory would satisfy our security mandates. Is there a way to make this work with database roles?
Is there a better way?
Thanks for the time and effort,
Mike
Thursday, September 6, 2012 11:08 AM | 1 vote
Hi Mike,
You need add user to db_datawriter database role, this will allow user to DELETE, INSERT, UPDATE data in the database. You need db_ddladmin to CREATE, ALTER or DROP stored procedures, alternatively you can grant permissions CREATE, ALTER and DROP procedure permission on the database as follow:
USE [Your Database]GOCREATE USER [SQLUser] FOR LOGIN [SQLUser]GOEXEC sp_addrolemember N'db_datareader', N'SQLUser'GOEXEC sp_addrolemember N'db_datawriter', N'SQLUser'GOEXEC sp_addrolemember N'db_ddladmin', N'SQLUser'GO
Regards,
Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)
Please remember to click
"Mark as Answer" on the post that helps you, and to click
"Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
Thursday, September 6, 2012 11:45 AM
Thank you for the time but that does not answer the question. The DML operations are not the issue and I apologize for not explaining that. The issue is that giving db_ddladmin allows other permissions. The idea is to only allow CREATE, ALTER, DROP (DML only SPs) on stored procedures only and preferably through a database role. Is that possible?
thanks again,
Mike
Thursday, September 6, 2012 12:09 PM
Hi Mike,
If you only want to grant the user permission to create, alter or drop procedures using user defined database role then you can achieve this using the following script:
USE [Your Database]GOCREATE ROLE [RoleName1] AUTHORIZATION [dbo]
GOCREATE USER [SQLUser2] FOR LOGIN [SQLUser2]GOEXEC sp_addrolemember N'RoleName1', N'SQLUser2'GOGRANT CREATE PROCEDURE TO [RoleName1]GOGRANT ALTER ON SCHEMA::dbo TO [RoleName1]GO
Regards,
Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)
Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
Thursday, September 6, 2012 12:35 PM
Thanks again for the reply but I am under the impression GRANT ALTER ON SCHEMA grants permissions for other DDL operations. The user must be restricted to stored procedure DDL only.
Thursday, September 6, 2012 1:00 PM
GRANT ALTER ON SCHEMA only allows user permission to add objects to that schema for which he has permission to. For example, in our code above we granted user the permission to create, alter and drop procedure. If that user tries to create the TABLE, VIEW or FUNCTION in the dbo schema he will get error that he hasn't got necessary rights to complete this task. See the demo for user Tom below:
In the following code, I created the login called Tom on my server and then created its User inside Orders database. After that I made the user Tom, member of RoleName1 (remember the RoleName1 is a database role and are created using the script on my previouse post) in the Orders database:
USE [master]GOCREATE LOGIN [Tom] WITH PASSWORD=N'', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFFGOUSE [Orders]GOCREATE USER [Tom] FOR LOGIN [Tom]GOUSE [Orders]GOEXEC sp_addrolemember N'RoleName1', N'Tom'GO
Now if I connect SQL Server using Tom, I can create, alter or drop stored procedure successfully but if I tried to create the table or view it gives me the following error message:
Regards,
Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)
Please remember to click
"Mark as Answer" on the post that helps you, and to click
"Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
Thursday, September 6, 2012 3:14 PM
The user cannot CREATE a table. But can you try dropping a table in that schema? My testing allowed this user to DROP a table in the schema. I am under the impression you can also ALTER any table in that schema.
thanks for the time,
Mike
Friday, September 7, 2012 9:02 AM
Hi Mike,
The only way to achieve this is by creating the DDL trigger. Here is the sample DDL trigger that will prevent DROP_TABLE for user
CREATE TRIGGER trigger_DDLON DATABASE FOR DROP_TABLE AS PRINT 'Dropping tables are not allowed' ROLLBACK
Regards,
Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)
Please remember to click
"Mark as Answer" on the post that helps you, and to click
"Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
Friday, September 7, 2012 9:43 PM
Thank you very much Basit for your time. However, the trigger would require more than that to ensure only DDL permissions on stored procedures. The preference is with database roles.
I ask this again - is there a way through database roles to give a user read/write acces to a database and full permissions on stored procedures (DML - stored procedures) only?
I also understand there are other solutions:
-
- Create a DDL trigger that only allows stored procedure DDL actions
- Create a separate schema where developers have full permissions
thank you for your time,
Mike
Monday, September 10, 2012 8:37 AM
Sorry Mike, there isn't any other way apart for the ones explained in my previous post.
Regards,
Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)
Please remember to click
"Mark as Answer" on the post that helps you, and to click
"Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
Friday, October 12, 2018 7:06 PM
I have a subscription database using transactional replication. I need to give report writes database reader role , create, execute stored procedure and views but they cannot make any schema or data alternations. How do i achieve this. I am clueless currently
Friday, October 12, 2018 9:33 PM
Don't piggyback on old threads. Start a new thread, and I would recommend you to explain your problem a little clearly. I think I understand what you want to do, but I am not sure.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se