Solved

Sql Permission

Posted on 2016-09-25
6
71 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 33

Expert Comment

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

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 28

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 250 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 49

Accepted Solution

by:
Vitor Montalvão earned 250 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 49

Expert Comment

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

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I have a large data set and a SSIS package. How can I load this file in multi threading?
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.
Viewers will learn how the fundamental information of how to create a table.

761 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