May 24, 2010 at 2:46 pm
I've searched these forums before for my question, and I've found a couple of times when its been asked, but I'm sorry, I don't understand the answers, and at least one of them was for an older version of SQL Server, so I'm going to ask it again.
Here's the scenario. We've got a fellow coming in to do some contract work for us. My boss has asked me to create a SQL Server account for him, which will prevent him from modifying any table's structure, but allow him to create and edit views, and create and edit stored procedures.
My first thought was to enter the following command:
GRANT CREATE VIEW TO username
that worked fine. However, the following did not work:
GRANT ALTER VIEW TO username
When I looked into it, as best I can gather it looks as though I've got to give the user some permissions we don't want to give the user, like make them a member of db_ddladmin. (If I understand it correctly, anyone in the role of db_ddladmin can run any DDL command, which we don't want them to be able to do.)
So, is it possible to grant a SQL Server user the ability to create views and edit views?
Or is it only possible to grant the user the ability of creating their own views and only the views they create?
Or am I going about this the wrong way? A few minutes ago it occurred to me that perhaps what I should do is give the user normal, public access to all of the dbo schema, so they can SELECT, INSERT, UPDATE and DELETE records from any table or view, and run any SP, that dbo owns, but not allow them to create anything in dbo. Perhaps I should instead create some new schema, that they can go ahead and create and edit whatever objects they want in? Is that the way I should do this? Or should I do what I was originally thinking of doing, and that's make it so this user can create new views and SP's, and edit any new or existing views and SP's?
Kindest Regards, Rod Connect with me on LinkedIn.
Viewing post 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply