DDL Viewer

Question

DDL Viewer

J-F Bergeron

SSCrazy Eights

Points: 8593
More actions
October 26, 2009 at 11:58 am

#212973

Hi all,
Is it possible to give access to the DDL of the functions, procedures, tables, etc, but don't give the right to change any of that?
I've seen I can create a database role where I can go one by one and select "view definition" but I really don't want to handle all the changes on these procedures, I got over a thousand to check.
Is there a role, or any way I can do this in bulk?
Thanks in advance,
Cheers,
J-F

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

You should be able to do view definition to the schema(s).

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Lowell SSC Guru Points: 323518 More actions · Answer 2

there is this command:

Grant View Any Definition To MyUserGroup

but there is nothing granular, so if you wanted to do, say grant view definition of procs but not views and functions, you have to assign permissions at each object...maybe with a cursor, but it's per object.

Grant View Any Definition To MyUserGroup will give it all in a single command, if that's what you need.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 3

Sorry I could not answer faster, I was in a meeting.

Nice guys, thanks a lot, this looks like exactly what I need, I will look into this.

Thanks again,

Cheers,

J-F

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 4

Guys, this works perfectly, we've tested this, and this is just what I needed.

I got a question though, I'm not really strong security wise, maybe that's why.

I'm looking from some place to query the rights the user have by security, securable, or I don't know what kind of breakdown we can have, but I'm really curious as to how I would have made that change with the SSMS interface? I changed it programmatically, but can't seem to find anywhere I see the right given, even though it works. This must be stored somewhere, or can be edited through the Studio. I bet Microsoft has a nice tool for that, or maybe I'm mistaking?

Thank you for any further input,

Cheers,

J-F

Silverfox SSC-Insane Points: 23150 More actions · Answer 5

in SSMS, look at server properties then look at permissions, it will be listed there, as you have allocated it at server level. what you have run, allows viewing in all databases.

--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 6

Thanks Silverfox,

From what I can see, I really have to go to the entity I gave the permissions from to see the access.

There is no way to see that for this specific user or group, I have given view definition on the server level, read access on the database level, and a write permission on a specific schema all at the same place. Or there must be a report for this.

I will search more on the subject, as we want specific reports on this, to ensure everyone has the correct security.

Thanks for your time, it has helped understand the concept.

Cheers,

J-F