Grant alter procedure permissions

I have a request to create a database role with permissions to create and 
update stored procedures and functions but not change the table definition, 
keys, indexes, and triggers.

I created the role in the development database and gave it the following 
permissions:

Create Procedure
Create Function
Alter Any Schema
View Definition

With this role the developers can create and modify stored procedures and 
functions. However, they can still perform certain edits on tables (eg. they 
can drop foreign keys but cannot create them. Reading on BOL, I see the 
objects in the schema securable scope include tables as well as functions 
and procedures.

Are there any permissions I can give that will deny changes to tables?


0
Loren
1/5/2010 7:18:24 PM
sqlserver.server 1327 articles. 0 followers. Follow

1 Replies
1672 Views

Similar Articles

[PageSpeed] 59

Loren Z (anonymous@discussions.microsoft.com) writes:
> I have a request to create a database role with permissions to create
> and update stored procedures and functions but not change the table
> definition, keys, indexes, and triggers. 
> 
> I created the role in the development database and gave it the following 
> permissions:
> 
> Create Procedure
> Create Function
> Alter Any Schema
> View Definition
> 
> With this role the developers can create and modify stored procedures
> and functions. However, they can still perform certain edits on tables
> (eg. they can drop foreign keys but cannot create them. Reading on BOL,
> I see the objects in the schema securable scope include tables as well
> as functions and procedures. 
> 
> Are there any permissions I can give that will deny changes to tables?
 
There are two routes you can take. One is to explicitly DENY ALTER on 
all tables, which is a bit tedious, and you need to remember it for new
tables.

The other option is to put procedures and tables in different schemas, 
and grant the developers ALTER on the schema where the procedures are.
Of course, this means that you need to change the code for all 
procedures to include a schema reference.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
1/5/2010 10:37:17 PM
Reply:

Similar Artilces:

Remote Procedure Call
I have a new laptop for work with XP Professional and XP Outlook. However, I have continuous problems with outlook just closing down for no reason. Other times it will remain open but as soon as I click on send the error message 'Remote Procedure Call failed' and then outlook closes. Somebody else within our company has now upgraded to XP and also suffers exactly the same problem. It has completely stumbled our IT manager and a third party IT specialist. Could you shed any light? PLEEEEEEEEEEASE!!!! Once the problem has occurred Outlook will continue to fail. The only fix ...

permissions for restart_sql_server.bat
I=92ve got a restart.bat that will shutdown and then startup the sql server 2005 process once a week. I put it in task scheduler and it runs if I give the account its running under (sql_maint) administrator permissions. But I didn=92t want to give that account admin permissions. Is there another group that I should use to get this to run, or is there a way to configure the sql server service to allow a certain user or group to shutdown and startup the service? Thanks in advace, Jim serveradmin Members of the serveradmin fixed server role can change server-wide conf...

Exchange 2003 Mailbox permissions acting strangely
We migrated to Exchange 2003 several months ago. Now my users are reporting that everyone has access to everyone else's mailbox--inbox, calendars, etc. Needless to say, people aren't happy about this. When I look at the properties of my mailbox and choose the Permissions tab, the "Default" and "Anonymous" permissions are both set to "None". I don't see how anyone could be accessing my mailbox. Yet I created another, completely unprivileged account for myself and verified that when logged in as this user I could access my inbox, calendar, etc....

Permissions and Security groups for international Org.
We are currently in Mixed-Mode for Exchange 5.5/2003 & Windows 2000/2003 and are planning to go native on both. The problem I've found, having joined the company only a few months ago, is that there are a lot of user accounts with permissions all over the place in AD & Exchange. I think this could have been caused by the ADC server currently being used, but I can't be sure. Anyway, my goal is to cleanup these rights and only have groups assigned rights. Here is my problem, we are an international company with offices in 65 countries and with techs that vary a great ...

Calling Procedures from another worksheet
I would like to know how to call a procedure from another worksheet in the same workbook. I can call a procedure from procedures in the same worksheet, but not if the procedure I'm calling is in another worksheet. What I have is the following in one worksheet... Private Sub LastEntry_Click() (instructions) End Sub And this in another worksheet.... Private Sub ClosePricing_Click() (instructions) Call LastEntry_Click End Sub Even If I change Private to Public or omit the words Private, it still does not work and the helps does not make it clear what I'm doing wrong. Rob Rob, App...

folder permissions
I just installed OL2002 and now I when I try to add or change permissions on any folders it is disabled or grayed out. Any ideas? Thanks in advanced! ...

Giving a user permissions to all public folders
Is there an easy way to give a user Edit "All Items" permissions to all public folders without having to go into each one manually? I'm on Exchange 2003 and have about 700 public folders. Thanks, Sander I'm sure pfdavadmin can do this for you...give permissions at the top of the tree and then "Propagate folder ACEs"...if it doesn't work at "All Public Folders", you might have to do it at each "top level" folder... "Sander" <noemail@noemail.com> wrote in message news:On9KLA2eFHA.612@TK2MSFTNGP12.phx.gbl... > Is ther...

Group permissions for Doc Library-make available to Site?
Within my site I created a document library. In that library I created a permissions group. Now I realize I actually needed that group created the Site level, not within the document library. It contains 75 members; recreating it would be needlessly tedious. Also, even if I wanted to do that, Sharepoint won't let me re-create it at the site level using the same name because "a group by that name already exists." But if it already exists, why does it not show up when I view the Site's Users and Groups? When I do that, it says the site has no users or groups. How can a...

You dont have appropriate permission to perform this operation.
In Outlook 2003 when I click on send and recieve it gaves me following error message: "You dont have appropriate permission to perform this operation." Please help Try checking the properties of the following files: outlook.srs, outlook.dat and views.dat. These files are located in: c:\document & settings\<Username>\application data\microsoft\outlook see if those files are set to read-only and, if they are, remove the read-only attribute. Post back on the outcome. "Khalid" <Khalid@discussions.microsoft.com> wrote in message news:53719C3A-BBDC-47EE-...

SELF permission setup
Can someone help me on how to set up the SELF account with the 'Associated external account' permission on the organization level? Also how to change the configuration so new accounts will have the permission as the accounts are created. Thanks and have a great weekend everyone. -Mike http://www.msexchange.org/tutorials/Understanding-External-Associated-Account-Windows-Server-2003-Exchange-2003.html Teo "Speedyturtle" wrote: > Can someone help me on how to set up the SELF account with the 'Associated > external account' permission on the organization l...

Permissions to Create Exchange Account
We have two AD domains (one is for our company and the other is for our sister company) in our Windows 2003 Forest. Our Exchange 2003 site resides in our domain called NET. Our sister company's domain is called SIS. We have most of the IT support here at our company and our sister company is somewhat technically challenged. How can we prevent our sister company's SIS\Administrator from being able to create mailboxes for their users. We don't care if they create their own user accounts but when a mailbox is to be created we need to be the ones to do it. Any ideas on how to re...

Strange issue with Exchange/Outlook Permissions
This one has really stumped me, no idea how to even start to troubleshoot this one... Working with an exchange 2000 enviroment. We have a user "UserA" who has a mailbox, working fine. userA went into his calender permissions, and added "userB" and "userC" to be reviewer's UserB was able to access the calender UserC isnt able to. On investigation, the UserC had disapeared, further testing shows when we add UserC to the calendar permissions, click OK. if we go back to the permissions he's disapeared. Both users are on the same server in the same mailbo...

EXCEL: Permission to wrkbk restrict:Inform. Rights Manag. config p
Dear All, I am using MS Office 2003 EXCEL and have already tried to and installed an EXCELff hotfix sent to me by Microsoft. The EXCELop file would not install. This is all due to the following problem with EXCEL 2003: When I attempt to open my EXCEL files with password protection on them I receive the following message: Permission to this workbook is currently restricted. Microsoft Office must connect to https://licensing.drm.microsoft.com/licensing to verify your credentials and download your permission. When I click on the OK button I see the initiation of a new box indicating ...

Grant Management Validation
When validating grants whether we select warning or prevent post we have the same problem: The AA Validation explains that we have exceeded a budget without telling us by how much. If I had 20,000 left in my budget and I wanted to spend 30,000 I am refused. However I would need to know that I am 10,000 over budget so that I could apply my expense against a different grant- or partially apply it. Has anyone found away around this problem? JP ...

Jet permissions and processes
Do users need a specific level of permissions in order for a Jet connection to be made to the database? Is there a system process that needs to be running in order for Jet to operate? Is it possible for someone (i.e. a network administrator) to block Jet connections or the Jet driver? If so, how would one go about determining that this has happened? Thanks; Amy The users require Change access (at least Read, Write, eXecute and Delete) on the folder where the MDB file exists. There's no system process that I'm aware of that needs to be running. There likely are ways to s...

Store permissions
Just looking for confirmation... Issue: Admin who had explicit send as and receive as against org object lost the ability to open anyones mailbox after I cycled inheretance at various levels in ESM. If I added him at the user level via Exchange advanced then he could access that persons mailbox. This person was a member of domain admins group which had an explicit deny at org and store levels. When I looked a user he couldn't access under advanced permissions I saw the denys for domain admins listed first. For a user for whom I permissioned via exannge advanced he had his allows ...

Advanced Security, Posting Permissions
Soooo we're in the middle of a SOX audit and the question has come up regarding posting permissions in advanced security. I know each section of the Advanced Security report lists posting permissions and I am under the impression those posting permissions are for the specific module (i.e. under Transactons -- Financial -- Series Post (Series Posting)). Auditors want to know what the difference is between the series posts and the posting permissions that print if you check the posting permissions checkbox when you run the report? Are they the same thing? Also, what contr...

Delegates folder permissions
We deliver email to desktop hard drives and do not store them on the EXCHANGE server. Is there a way for the delegates to see the subfolders under the Inbox? ...

Using Repair Permissions
Using Repair Permissions comes up quite often on this list. I ran across this on Apple Discussions today and thought I would pass this along. You do not need to Repair Permissions on a regular basis. There are only three occasions you need to do this: 1. When you have just installed something that required you to run an Installer, rather than just copying some software to a folder. This should be done for both Apple and non-Apple software. (Note Office 2008 will come with an Installer this time) 2. When you have been working on your OS X files or folders while booted from OS 9, or remote...

Strange Public Folder permission assignments
Hi, I just migrated a 5.5 server to a 2k3 one. The biz are still using the NT domain accounts to access the migrated resources. This is working fine for mailbox stuff, but the public folder access is a bit flakey. I gather this is because public folders are assigned permissions from the GAL, which refers to AD and not the NT directory. After much knashing of teeth i realised that if i apply permissions to the folders using mail enabled security groups (which contain the users in AD) then they have access to the folders. Wheras if i grant the individual user access - they get nothing.... Do...

Move Mailbox 55 to 2003 delegate/client permissions not working
Hi Hope someone out there can help. We recently started moving mailboxes from Exchange 5.5 to Exchange 2003. What we find is that users who remain on the old system and which had permissions to certain folders of a user moved to 2003 can no longer access these resources. Now here is the strange part, on this particular 2003 user, when you remove the permissions on Outlook and recreate them the 55 user can still not access the folder. BUT, if I assign permissions to a completely diff Mbx (previously migrated to 2003) on 2003 then the 55 user can access the resources. We have many users w...

Client Permissions in Public Folders
Is there a way to propagate Client Permissions throughout public mail folders? Thank you On Tue, 19 Jul 2005 09:27:26 -0700, Bartly <Bartly@discussions.microsoft.com> wrote: >Is there a way to propagate Client Permissions throughout public mail folders? > >Thank you Use ESM (Task/Propagate Settings) or pfdavadmin. Thanks "Andy David - MVP" wrote: > On Tue, 19 Jul 2005 09:27:26 -0700, Bartly > <Bartly@discussions.microsoft.com> wrote: > > >Is there a way to propagate Client Permissions throughout public mail folders? > > > >...

Error Uploading Reports not Permissions
Hi, I can't uploa my reports. Applied a solution. Add NT Authoring\Network Service account from url:crm/reports, but still not work. The complete error is: Server Error in '/' Application. ________________________________________ Exception of type Microsoft.Crm.CrmException was thrown. [CrmException: Exception of type Microsoft.Crm.CrmException was thrown.] Microsoft.Crm.Application.Platform.Report.InternalCreate(String xml) +721 Microsoft.Crm.Application.Platform.Entity.Create() +109 Microsoft.Crm.Application.Forms.AppForm.RaiseDataEvent(FormEventId eventId) +405 ...

Distribution List Permission Denied to Owner
Running XP Pro on the desktop and Exchange 2003/Server 2000 in the back office with Powerfuse for desktop management. I am having trouble with giving ownership of public distribution lists to users on our standard imaged machines. Users on those machines are denied permssion to make changes to groups. However, the same user on a non-imaged machine (no PowerFuse, either) do not get this error message. Any ideas? t. This will happen if Exchange refers Outlook to a GC outside of clients own domain. Search for "closest gc" or "ClosestGC" keywords and implement a GPO...

Unable to open default e-mail folders, lacking permission
Had an interesting situation. We run Exchange 5.5 with a 2K AD domain. We had a user this morning who was unable to open Outlook XP (though I don't think the client version matters). She runs Windows XP on her machine. She would get the error, "Unable to open your default e-mail folders. You do not have permission to log on." She was able to open Outlook without any problems up until recently and nothing had changed with her domain account or Exchange mailbox. It turns out another user had somehow stored their password (in her user profile) when logging on to just the Excha...