Solved

Sql Permission

Posted on 2016-09-25
6
43 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 32

Expert Comment

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

Expert Comment

by:Pawan Kumar Khowal
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 17

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 45

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 45

Expert Comment

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

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard 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.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now