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.