Skip to content

Grant Permissions to a Database for Windows Active Directory User or Group in Microsoft SQL Server

by on June 14, 2016

Your users may get errors performing certain functions required by their job, but not covered in the standard roles, such as DB_DataReader.

Error: SHOWPLAN permission denied in database “mydatabasename”

This is just an example, but often happens when users need to write and then evaluate the plan for queries, ie. do some optimization evaluations. Once you have identified the permission needed, and what it is needed to, it is just a matter of using the proper syntax!

SQL User

use MyDatabaseName
go
grant showplan to [mysqluser]
go

Windows Active Directory User

use MyDatabaseName
go
grant showplan to [mydomain\myusername]
go

Windows Active Directory Group

use MyDatabaseName
go
grant showplan to [mydomain\mygroup_r]
go

The users should now be able to use the additional features your have enabled!

 

Advertisements

From → Uncategorized

Leave a Comment

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

%d bloggers like this: