Security to Run DDL statements only via SSMS

Question

Security to Run DDL statements only via SSMS

indupriya9

Grasshopper

Points: 17
More actions
June 9, 2010 at 2:51 pm

#219767

I have a scenario where a user has access via a CRM application that is sql server based. This same user needs Management Studio access where I want to restric access to only run select statement. How can I achieve this?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

indupriya9 (6/9/2010)
I have a scenario where a user has access via a CRM application that is sql server based. This same user needs Management Studio access where I want to restric access to only run select statement. How can I achieve this?

i don't think you can; you can test whether a login has a HOSTNAME like 'SQL Server Management Studio', and deny access at login, but you cannot give a person more or less rights depending on which application they use. once they are in, the rights they ahve are, well, the rights they have.

the right thing to do is to create different roles; the CRM application should use a login that is attached to a role with more rights, and noone should be able to use that login because they should not know the password;

other logins would belong to roles with severely restricted rights for reporting or something only.

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!

freshcrop2010 Newbie Points: 9 More actions · Answer 2

thanks for sharing this statements

Data Recovery !! Image Recovery !! Photo Recovery !! MAC Photo Recovery

Lowell SSC Guru Points: 323518 More actions · Answer 3

freshcrop2010's post is Spam. Reported. No replies please. bot joined and posted within the same minute(second?) of the forum join.

Joined: Today @ 12:43:26 PM (0 visits since)

Local Time: 7/1/2010 6:52:14 PM

Last Login: Today @ 12:43:26 PM

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!

Ashwani Malik Ten Centuries Points: 1140 More actions · Answer 4

Try giving Select permissions on Filtered Views. Microsoft recommends to query Filtered views in CRM rather then tables directly.