Update SQL Permissions using SMO

It's surprisingly easy to set permissions in an object based way using SQL Management Objects (SMO). Tim Mitchell shows you how to use the ObjectPermissionSet to hold the permissions and then apply them to a specified object. If you're new to SMO check out Tim's other videos to learn SMO from the beginning!

Tim Mitchell is a Microsoft SQL Server database developer, business intelligence consultant, writer, and speaker. He has been working professionally with SQL Server for over six years, and holds the MCTS and MCDBA certifications from Microsoft as well as a Bachelor's degree in computer science from Texas A&M University - Commerce.

hades on 2/19/2009

is it possible to Script out the T-SQL code with SMO scripter and generate something like this: GRANT EXECUTE ON [dbo].[upsmyproc] TO [MYROLEROLE] AS [dbo] GO

Lou Gallo on 6/4/2009

Nice eye-opener. Just would be good to indicate what dll's need to be referenced and what 'using' namespaces are necessary.

Josh Blair on 6/4/2009

Nice and clear explanation. Thanks.

Suresh on 10/20/2010

Nice.It Will Be good Stuff For the Freshers

Suresh on 10/20/2010

Nice And It Will Be Very Useful To Me

D151BB6B9F on 10/24/2011

You should have shown the syntax for multiple permissions being applied to an object.

Craig Matthews on 10/25/2011

Please talk a little louder

De Brabander John on 11/9/2011

The first line spoken, gave the feeling that the speaker had already said something and this hadn't been recorded.