Restriction on DML Statements in SSMS

Question

Post reply

Restriction on DML Statements in SSMS

krishna.dhara

Grasshopper

Points: 23
More actions
April 27, 2014 at 11:52 pm

#286069

Hi,
I would like to know if there is any option to Restrict DML statements in SSMS for a user where the same user should be able to perform these actions through application on particular database.
Thanks,
Krishna Dhara

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

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

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

SSMS is neither the appropriate nor the intended interface for such restrictions.

😎

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

To further add to Eirikur's comments, sql does not care which application is connected to it. You don't control permissions by where the connection was originated. You control permissions by user. You could possibly use a different sql login from the application. That could effectively achieve what you are after.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

I haven't used it, so I'm not sure that it would work but Application Roles might help.

http://technet.microsoft.com/en-us/library/ms190998.aspx

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 4

You've just defined what stored procedures are used for. You give access to insert data through a stored procedure, but not through the table directly. That way you can control, very directly, who does what and how they do it to ensure your system works the way you think it should.

In addition, I do like using roles instead of individual logins as a way to manage security. Further, instead of individual logins, I use AD groups. That way, I map an AD group to a role (or roles), and then the individual users are added or removed from the AD group and automatically added or removed from security within SQL Server.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning