Grant Create View Ability Without Over Permissioning

Grant Create View Ability Without Over Permissioning

Post by Mark » Thu, 03 Sep 2009 00:45:31


Thanks in Advance.

I have a need to provide a user who has read-only access the ability
to create and alter views.

We are on MS SQL Server 2005 and use an ERP system that locks the
schema so that most objects cannot be altered, yet we can suplement.
All objects are part of the dbo schema.

I provided the user Grant Create View Access, but he received the
following message:

"The specified schema name "dbo" either does not exist or you do not
have
permission to use it. "

I later ran the following command:

GRANT ALTER ON SCHEMA::dbo TO "DOMAIN\user"

The user was then over permissioned and could drop tables.

Could someone point out how I can provide more granular access?

Mark S
Napa, CA
 
 
 

Grant Create View Ability Without Over Permissioning

Post by TheSQLGur » Thu, 03 Sep 2009 04:17:56

Maybe you could craft a special DDL trigger to prevent the user from
actually creating any objects except views.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

 
 
 

Grant Create View Ability Without Over Permissioning

Post by Mark » Thu, 03 Sep 2009 06:32:42


Thanks for the idea Kevin. I am really looking to prevent someone
with this authority from dropping other objects. I have not yet
tested use of INSERT, UPDATE, and DELETE on the database.

DDL looks like the wrong way to handle. I am just trying to avoid
having to write a series of REVOKE statements for everything but
SELECT and CREATE/ALTER VIEWs. It seems a reasonable business
situation that you would grant these kind of permissions. Granting
full ALTER rights on the schema seems a bit much.

Mark S
 
 
 

Grant Create View Ability Without Over Permissioning

Post by Jeffrey Wi » Thu, 03 Sep 2009 10:47:45

How about creating a schema for the user (owned by dbo) and grant the level
of access you want to that schema only? Would that work?
 
 
 

Grant Create View Ability Without Over Permissioning

Post by Mark » Fri, 04 Sep 2009 03:54:41

On Sep 1, 6:47m, "Jeffrey Williams" < XXXX@XXXXX.COM >







That **sounds** promising. Can I have the dbo schema have higher
priveleges than the subordinate user schema that still allows CREATE/
ALTER VIEWS without the ability to DROP objects? Writing this out
makes me think that I would have to assign all the views to this
subordinate user schema to get it to work right.

Gives me something to check out. Thanks.

Mark
 
 
 

Grant Create View Ability Without Over Permissioning

Post by TheSQLGur » Fri, 04 Sep 2009 04:22:46

Well, at least you can script out the sql statements using system objects
and some dynamic sql.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net








Thanks for the idea Kevin. I am really looking to prevent someone
with this authority from dropping other objects. I have not yet
tested use of INSERT, UPDATE, and DELETE on the database.

DDL looks like the wrong way to handle. I am just trying to avoid
having to write a series of REVOKE statements for everything but
SELECT and CREATE/ALTER VIEWs. It seems a reasonable business
situation that you would grant these kind of permissions. Granting
full ALTER rights on the schema seems a bit much.

Mark S