SQL Server Permissions

sqlserver2014

Understanding SQL Server permissions seems to be something that people new to SQL Server have a problem understanding. You have network permissions, server permissions, database permissions, schema permissions, etc. In a recent article by Don Kiely, he explains SQL Server permissions very well.

Just about every user-definable object in SQL Server is a securable object, something that you can control access to using permissions granted to a principal. There are three scopes of securable objects—server, database, and schema—that form a hierarchy of securable objects in an instance of SQL Server.

There are three permissions statements that you can use. A GRANT statement grants a permission on a securable object to a principal. A REVOKE statement undoes what the GRANT statement does: it removes a permission on an object previously granted to a principal. If the principal doesn’t have the permission when the REVOKE statement executes, the principal still doesn’t have the permission, and no error occurs. It’s important to understand that a revoked permission can still be inherited through membership in a role that has the permission. This can cause some rather convoluted situations, so a best practice is to rarely grant permissions to individual users, logins, or other single principals, only to roles.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s