Granting Least Permissions

I recently saw a note about creating Sql Server Authentication logins allowing users to see the content in a database through views created there. My friend was with very little information able to create the correct logins with passwords and was also able to get the permissions set up so the users could indeed user the views to do their work. I congratulated her on her work. Good Job!

I then listened while she proceeded to tell me that she had to grant the VIEW SERVER STATE permission to the new logins in order to get them to work. My only question at that point was, “Why?!”

During the course of creating the new logins and users she was getting an error and that was the only way to get rid of it. Hmmm. “What was the error?”

Error:

TITLE: Microsoft SQL Server Management Studio

——————————

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

ADDITIONAL INFORMATION:

VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’. (Microsoft SQL Server, Error: 300)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.4100&EvtSrc=MSSQLServer&EvtID=300&LinkId=20476

——————————

‘Okay’, I thought. ‘This is straight forward enough.’  I started to ask stupid questions, ‘I’m good at those.’

I asked, What database are the views in the users need?” “CustomerManagement”, was the response. Good so far.

“Did you create users for the logins in that database?”, was my follow-up. “Yes.”

“Do the users need to work with system DMVs or functions?”, was my next. “No.”

“Did you change the login default database to the CustomerManagement db?”, was next on my list. “No.”

“Okay, let’s try a little test… I have my handy local instance here on my workstation. I think we can get past the question of whether they really need view server state.” Here is the example code we came up with…

—  1.  Create Login
Create Login test_user
    WITH    Password         = x@x0x$xx,
            Default_Database = DBAsOnly,
            Check_Expiration = ON,
            Check_Policy     = ON;
Go

—  2.  Create db user
Use DBAsOnly;
Go

Create User test_user For Login test_user;
Go

/***************************************************/
—  3.  Create user view, Unless already created.
Create View dbo.vw_TestView
As
    Select  *
    From    dbo.Testme
Go

Grant SELECT On vw_TestView To test_User As dbo;
Go
  Note: If there are many views and multiple users
—        it is usually easier to manage by creating
—        database roles, adding the users to them
—        and granting Select permissions to the role.

/***************************************************/
—  Test
Execute As User = ‘test_user’;
Go

Select  User_Name ()
Go

Select  *
From    dbo.vw_TestView
Go

Revert;
Go

Select  User_Name ()
Go

Once this had been run my friend was happy I had shown this to her as she had been using the GUI tools in SSMS to do her work up until now. She had thought it was too difficult to do things with T-Sql. With small bit she was able to see how easily new users could be added and granted permissions. I happily assured her that not only can T-Sql do most of the things that you can do with the GUI tools but she wouldn’t have to worry about accidentally clicking the wrong checkbox when working and doing something painful to her systems.

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