Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Sql Permission

Posted on 2016-09-25
6
Medium Priority
?
98 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 35

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 35

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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 52

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 52

Expert Comment

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

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

916 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