SQL Server 2005 - View Access on Database Stored Procedures and Views

Question

SQL Server 2005 - View Access on Database Stored Procedures and Views

Libby

Ten Centuries

Points: 1175
More actions
April 12, 2010 at 12:28 pm

#386684

Is it possible to allow view only access on stored procedures and views in a database? I have view access to the tables, but when I attempt to "Script View as" - "Create to" - "New Query Editor Window", I'm getting a message that indicates there are insufficient access rights. I'm in MS Sql Server Management Stuido. If it is possible, can this permission be granted to all sp's and views in the database or does the permission have to be set up on each one individually?
Thanks for your help.

Viewing 10 posts - 1 through 9 (of 9 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Yes, the permission can be granted to all in a single statement. However, the preferred method is on a proc by proc basis.

You should speak with the administrator that locked down the accounts and verify the permissions levels.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Libby Ten Centuries Points: 1175 More actions · Answer 2

Thanks for your reply Jason. I appreciate it!

Yes, my mgr is okay with me getting this access and was going to speak to someone else about how to get it. It doesn't appear to me that the "dba" I work closely with, knows a lot about security on databases. She was able to give me enough access to at least see the sp in the list - but I can't actually create it to a query script window to view the detail of it. The last place I worked at, I was able to view sp's and views without having full rights to update objects.

If it's not too much to ask, can you please tell me what the command would be to grant it at the database and object levels?

Thanks so much for your help.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

The permission to modify procs is different than the "execute" permissions that you appear to have been given.

You need "view definition."

Are you a developer or a jr dba?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Libby Ten Centuries Points: 1175 More actions · Answer 4

I am a developer currently creating stored procedures, etc. for Crystal reports. I am not a dba.

I know I don't have "Modify" permissions because this option is grayed out. I do have "Script Stored Procedure as - CREATE TO - New Query Editor Window", on the sp's I can see, but when I attempt to run it to output the script to a new window, I'm getting an error that indicates I don't have rights. The same thing is true with the Views I can see. It's a little different, but basically the same where I'm getting an error when I attempt to run the process to output the script to a query window.

I guess I have 2 separate issues going on here:

1) I need to be able to see in the list, ALL sp's and views in the database.

2) I need to be able to select from the list, any of the sp's or views and create the code to a new query window.

Will the permission to 'VIEW DEFINITION' accomplish what I'm needing?

Thanks again for your time.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

View Definition will work for the procs. For the views, that subject is more delicate. Views are handled differently and thus would be subject to a different set of access rules. Your company may want to find an alternative - unless this is a development server.

To be able to script the views you need the "Create View" permission and the "Alter" permission on the schema. This level of permissions grants you too many rights in the database - beyond just scripting the view.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Libby Ten Centuries Points: 1175 More actions · Answer 6

Thanks so much for your help Jason! I appreciate your time.

Libby

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

You're welcome.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

P Jones SSChampion Points: 12361 More actions · Answer 8

Why not ask your DBA for a script of the database tables, views and procs? Or for an empty copy of the database created from a script on which you can have rights without breaking anything?

joy.priyank SSC Rookie Points: 41 More actions · Answer 9

Hi Jason,

Could you please help me on my one problme in Sql server 2005.

I have 1 database in sqlserver 2005. i want to give permission to the user to access all database View of this database.

I have created a database role and defined all database view on this database role. and have created one user and provide a permission to this database role.

but when this user use their login, then it can access all database tables and database view of this database

Could you please help me, and let me know how can i create such type of user that they can only access database view of any particular database.

your help would much be appricate.

Thanks & regards

Priyank