Read only Rights with Create View - Help

Question

Read only Rights with Create View - Help

aliciakw

SSC Eights!

Points: 802
More actions
January 10, 2008 at 9:48 pm

#178096

I am trying to set up permissions in SQL 2005 to give a user Read Only rights to the table, but he needs to be able to create views. I have assigned him to the public role and modified the Database securibles to Granted him Create View rights, but when I connect from a remote server and try to create a view I get the error: CREATE VIEW permissions denied in database 'test01'.
Any help on how to resolve would be greatly appreciated, I have spent several hours testing tonight with no luck. :crying:

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

I dont know if this will help but

You could always give the user DDL rights. After that you can add the user to the DB_denyDataWriter role along with db_Datareader role.

That will give the user right to create and read but cannot do DML statements.

-Roy

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

Roy's solution is interesting. How should the user create a view without seeing the table?

Why not just have them request a view from the DBA? Surely they're not doing this on a regular basis.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

You can create a stored procedure with EXECUTE AS 'user' that creates the view, and grant them execute permission on the stored procedure. It would not be necessary to grant the user the write to create views. The user context for the procedure would have to have the necessary permission to create views, grant access to the view, etc.

You may have to do a little work to make sure that they don't slip some other DDL or other unwanted code into it.

aliciakw SSC Eights! Points: 802 More actions · Answer 4

Great suggestion. I went in and made the changes to his rights, giving him ddl_admin rights, db_datareader rights, and dd_denydatawriter rights. When I go in to do testing, I am now able to create views using this login, but I am also able to delete tables. I did not take testing any further as this will not work, I can not trust this user with rights that will allow him to delete tables. His permissions should only be to read the database tables and create views.

He will be creating these views periodically and unfortunately he has gotten approval to go over my head and get connection rights to the database instead of putting in a request to me so that I can create the views. Any other suggestions welcome.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 5

I hate it when programmers goes behind the back of DBA to get permission on DB's.

What you were doing might also work. But you will need to give two additional permission.

You have to give him CREATE VIEW permission in the database and ALTER permission on the schema in which the view is being created.

Also give him db_datareader access as well so he can select from tables and the views that he just created.

-Roy

aliciakw SSC Eights! Points: 802 More actions · Answer 6

Thanks again Roy. I have tried your suggestions and all functions work as I want except the Drop Database statement. There seems to be no happy medium for me.

I have tested creating tables and got permissions denied as expected and wanted, altering tables with the same result..etc. Again all testing is working fine with your suggestions except the DROP TABLE function, it allows the user to drop the table and that is a function that I just can't feel easy about him having. Any other suggestions are greatly appreciated and thanks for the time you are taking to assist me on this mind boggling issue.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 7

As per BOL, a user requires Schema Alter permission along with either CONTROL permission on the Tables or Db_DDLAdmin role. Can you double check if you have given the user Control permission on the table?

-Roy

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

What about different schemas? Meaning - grant this person "straight" datareader permissions on the default schema, but create/update/delete within a separate schema.

Edit: nice thought in theory but it doesn't seem to work. The rights from multiple schemas seem to combine, so the grants and denies tend to bleed through...

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Adam Haines SSC-Insane Points: 23217 More actions · Answer 9

You want to use least privilege and combining roles and explicit permission can quickly become convoluted. Just use something as simple as this

USE [DB]

GO

GRANT SELECT ON [dbo].

TO

GO

USE [DB]

GO

GRANT CREATE VIEW TO

GO

This should give the user the create view ability only select on the needed table. When he/she tries to create a view he/she can only do so on the table to which he has permission. Just make sure he has connect and is part of the public role.

My blog: http://jahaines.blogspot.com

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

aliciakw123 (1/10/2008)
I am trying to set up permissions in SQL 2005 to give a user Read Only rights to the table, but he needs to be able to create views.

As an actual DBA, you would allow a user or even a developer to create views in a production database? Do you have any idea how badly a single poorly written view can cripple the database?

This is a very bad idea.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

aliciakw SSC Eights! Points: 802 More actions · Answer 11

Thanks everyone for the suggestions, and yes, I am aware that this person can create a view that will cripple the system, you are preaching to the choir, but again, he has gone over my head and gotten approval from Executive Level staff. I was able to block his request to schedule jobs to run these views, but the rest was out of my hands. I did finally find a solution.

I assigned the users to the public role for the database, then went into the database security and granted securables for Schema dbo, and the database. On the schema, I selected the following rights: Grant - Alter and Deny - Delete. On the database, I gave rights to create view. After hours of testing, it seems that this combination will not allow the user to deleted, insert or alter any tables or data, but will allow the user to create views.

Thanks again. All of the suggestions were greatly appreciated and a huge help in leading me to the answer that worked best for the unfortunate situation I was presented.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 12

Thanks for the feedback on what you did.

Heh... I sometimes forget just how lucky I am... we "clone" the database everynight at midnight to a "do what you want, users" data base. We've also given them a "Work" database where they can create whatever they want, even bad views (although we do try to help them in that area). Keeps them happy with almost-current data and keeps us happy because they're not impinging on the production database. To top it off, management is real supportive of keeping the users out of the production database because of the clone we make nightly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

arunkumar.c Grasshopper Points: 17 More actions · Answer 13

Hi Guys,

I need some information in MS ACCESS.i am using server client configuration in my project.In server PC database is available.In client PC is accessing the main database from server only.I need to create one reports(weighbill) in cilent PC.It should not allow to prepare the final reports(final weihbill) in Server PC until the reports created in the client software.

andre.quitta SSCommitted Points: 1921 More actions · Answer 14

I didn't have any success with Adam Haines' approach. When trying to test this I got the error:

[font="Courier New"]The specified schema name "dbo" either does not exist or you do not have permission to use it.

[/font]

I think Lowell had a reply here for the convoluted way to make it work:

http://www.sqlservercentral.com/Forums/Topic1241211-391-1.aspx

It's not my first preference. I'd rather create a view and then let a group go to town on it. It's more obvious that way.