Prohibition of users to Create/Alter tables from UI Designer.

Question

Post reply

Prohibition of users to Create/Alter tables from UI Designer.

Paresh Prajapati

SSCertifiable

Points: 7567
More actions
December 25, 2008 at 12:50 am

#129495

I want to do following :
Use can not alter or create table from UI designing.
They can alter/create table from running query in query analyzer but not directly from UI..
So, how can i prohibit to them?
_____________________________________________________________________________________________________________
Paresh Prajapati
➡ +919924626601
http://paresh-sqldba.blogspot.com/[/url]
LinkedIn | Tweet Me | FaceBook | Brijj

Viewing 12 posts - 1 through 11 (of 11 total)

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

Lowell SSC Guru Points: 323506 More actions · Answer 1

Here's my first guess, and it's probably right:

when they are coming thru your UI, they are using a different login than when they connect vi SSMS....

if i connect with user "webdev" thru your UI, but connect as a user in BuiltIn\Administrators via SSMS/QA, you can see how my permissions would differ ;

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!

UnionAll SSCrazy Points: 2102 More actions · Answer 2

Paresh Prajapati (12/25/2008)
I want to do following :
Use can not alter or create table from UI designing.
They can alter/create table from running query in query analyzer but not directly from UI..
So, how can i prohibit to them?

My gut feeling is that it is not possible to acheive this.

Resaon: A user cannot have different privileges with UI and Query Analyzer.

Regards,

Sam.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Is this for SQL Server 2005?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Paresh Prajapati SSCertifiable Points: 7567 More actions · Answer 4

Yes it is sql server 2005.

I want user can change table structure from query anlyzer only by scripts.

_____________________________________________________________________________________________________________
Paresh Prajapati
➡ +919924626601
http://paresh-sqldba.blogspot.com/[/url]
LinkedIn | Tweet Me | FaceBook | Brijj

Miron Berlin SSC-Addicted Points: 414 More actions · Answer 5

see if you can achieve it using DDL trigger by checking application name trying to execute alter table.

Paresh Prajapati SSCertifiable Points: 7567 More actions · Answer 6

Miron Berlin (12/26/2008)
see if you can achieve it using DDL trigger by checking application name trying to execute alter table.

How can I get appliocationName from DDL trigger?

Can u explain process by code or any?

_____________________________________________________________________________________________________________
Paresh Prajapati
➡ +919924626601
http://paresh-sqldba.blogspot.com/[/url]
LinkedIn | Tweet Me | FaceBook | Brijj

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Like this:

IF APP_NAME() = N'Microsoft SQL Server Management Studio'

BEGIN

ROLLBACK TRAN

RAISERROR 'You are not allowed to edit tables with the GUI', 15, 1

RETURN

END

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

rembersu-432095 Ten Centuries Points: 1300 More actions · Answer 8

I think you can try giving the user only read,write,DDl-Admin previlige this will

allow the user only to modify by query. if you add the View Definition permission on the

database level then the user will be able to modify from the UI aslo.

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 9

I think that this can be done thru an Application role....Isnt it...

Regards,
[font="Verdana"]Sqlfrenzy[/font]

Paresh Prajapati SSCertifiable Points: 7567 More actions · Answer 10

Thanks guys

_____________________________________________________________________________________________________________
Paresh Prajapati
➡ +919924626601
http://paresh-sqldba.blogspot.com/[/url]
LinkedIn | Tweet Me | FaceBook | Brijj

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

glad we could help.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]