?
Solved

Sql Permission

Posted on 2016-09-25
6
Medium Priority
?
106 Views
Last Modified: 2016-10-27
Hi, I need to block if any one trying to create Stored Procedure and View.
even 'sa' users.
I understand it's need to be done Server Object-> Trigger.
0
Comment
Question by:ukerandi
  • 3
  • 2
6 Comments
 
LVL 36

Expert Comment

by:ste5an
ID: 41815003
Nope. You simply revoke that permissions from all users. And disable sa.
0
 
LVL 38

Expert Comment

by:Pawan Kumar
ID: 41816175
Create a role called "StoredProcedureRole" and provide CREATE PROCEDURE permission to that role.

Now assign that role to a user which you want to create stored procedures.

Now based on the roles you can allow users to perform operations..
0
 
LVL 38

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 1000 total points
ID: 41816198
Continue with the above comment.

yes you are right you can create a trigger. sample code

--

CREATE TRIGGER trgCreateStoreProcedure 
ON DATABASE FOR Create_Procedure 
AS 
BEGIN 
   IF IS_MEMBER('StoredProcedureRole') = 1 
   BEGIN 
       PRINT 'You cannot create procedure(s).'
       ROLLBACK TRAN
   END
END
GO

--

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 53

Accepted Solution

by:
Vitor Montalvão earned 1000 total points
ID: 41819307
You can simply deny the creation of those objects for the specific users:
USE databaseName
GO
DENY CREATE PROCEDURE TO [UserName]
DENY CREATE VIEW TO [UserName]

Open in new window

If you have many users you can just create a role where you include the users and for the above command replace UserName with that role name.
0
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 41839704
ukerandi, a feedback will be appreciated.
Cheers.
0
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 41860026
ukerandi, you are also an Expert and you should know that the etiquette is to return to the opened questions and give a feedback.
Please let us know if you still need help with this question.
Cheers
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question