Feeds:
Posts
Comments

Archive for the ‘Security’ Category

Quick one today:

Every now and then, when good Sql Server resources are available, they are posted here for more people to benefit from them. Continuing on that tradition, today we have a gold mine. Microsoft has released many, many & many e-books open to public to download for free. In the list there are several Sql Server books along with BI, Windows, Office, SharePoint, etc

Happy learning !!

 

Hope this helps,
_Sqltimes
Advertisements

Read Full Post »

Sql Server allows assigning or GRANTing different applicable permissions to different objects. Since there are so many nuances, sometimes it gets confusing. So this cheat sheet helps reduce the confusion and always come in handy when assigning permissions to securables. Some are listed below:

  • Tables
  • View
  • Type
  • Stored Procedure
  • Functions
  • Synonym
  • User
  • Role
  • Schema
  • Database
  • Login
  • Server

Tables:

  • ALL does not mean all possible permissions. For tables it only means DELETE, INSERT, REFERENCES, SELECT, UPDATE.
  • Using the phrase ‘OBJECT’ is optional, but a good practice to follow –  it helps with other objects.
--
-- Grant permissions to tables
--
GRANT SELECT                     ON OBJECT::dbo.StoredValue    TO SomeUser
GRANT SELECT, UPDATE, DELETE     ON OBJECT::dbo.StoredValue    TO SomeUser
GRANT ALL                        ON OBJECT::dbo.StoredValue    TO SomeUser
GO

Views:

  • ALL does not mean all possible permissions. For tables it only means DELETE, INSERT, REFERENCES, SELECT, UPDATE.
  • Using the phrase ‘OBJECT’ is optional, but a good practice to follow –  it helps with other objects.
--
-- Grant permissions to views
--
GRANT SELECT                   ON OBJECT::dbo.vw_StoredValue    TO SomeUser
GRANT SELECT, UPDATE, DELETE   ON OBJECT::dbo.vw_StoredValue    TO SomeUser
GRANT ALL                      ON OBJECT::dbo.vw_StoredValue    TO SomeUser
GO

Type:

--
-- Grant permissions to views
--
GRANT VIEW DEFINITION          ON TYPE::dbo.SSN                TO SomeUser
GO

 

Stored Procedures:

  • ALL does not mean all possible permissions. For stored procedures it only means EXECUTE.
  • Using the phrase ‘OBJECT’ is optional, but a good practice to follow –  it helps with other objects.
--
-- Grant permissions to views
--
GRANT EXECUTE                 ON OBJECT::dbo.usp_Expired_StoredValue      TO SomeUser
GRANT ALL                     ON OBJECT::dbo.usp_Expired_StoredValue      TO SomeUser
GO

Functions:

  • Assigning ALL permissions only means the following for functions
    • Scalar function permissions: EXECUTE, REFERENCES.
    • Table-valued function permissions: DELETE, INSERT, REFERENCES, SELECT, UPDATE.
--
-- Grant permissions on functions
--
GRANT EXECUTE                 ON OBJECT::dbo.udf_WhatIsMyAge              TO SomeUser
GRANT ALL                     ON OBJECT::dbo.udf_CalculateAge             TO SomeUser
GO

 

Synonym:

  • When dealing with Synonyms we still need to follow the same ownership-chain. Along with permissions to use synonym, we also need permissions base objects.
--
-- Grant permissions on Synonyms
--
GRANT EXECUTE                  ON OBJECT::dbo.Syn_sp_TestProc              TO SomeUser
DENY  EXECUTE                  ON OBJECT::dbo.Syn_sp_TestProc              TO SomeUser
GO

 

User:

  • A user is a database-level securable contained by the database in which it exists.
--
-- Grant permissions on Users
--
GRANT CONTROL                 ON USER::SomeUser1                          TO OtherUser
GRANT IMPERSONATE             ON USER::SomeUser1                          TO OtherUser
</pre><pre>GO 

Role:

  • Just like user, a Role is also a database-level securable, in which it exists.
--
-- Grant permissions on Role
--
GRANT ALTER                   ON ROLE::r_ExecuteReportsOnWeekdays         TO SomeUser
GO

 

Schema:

  • A schema is also a database-level securable, in which it exists.
--
-- Grant permissions on schema
--
GRANT SELECT                  ON SCHEMA::HumanResource                    TO SomeUser
GRANT INSERT                  ON SCHEMA::ArchiveObjects                   TO SomeUser
GO

 

Database:

  • A database is a instance-level securable, in which it exists.
  • Several permissions could be assigned on a database; When we mention ALL, the following specific permissions are assigned implicitly:
    • BACKUP DATABASE
    • BACKUP LOG
    • CREATE DATABASE
    • CREATE DEFAULT
    • CREATE FUNCTION
    • CREATE PROCEDURE
    • CREATE RULE
    • CREATE TABLE
    • CREATE VIEW
    • SHOWPLAN
--
-- Grant permissions on database
--
USE [SomeDatabase]
GO
GRANT CREATE TABLE       TO SomeUser
GO

--
-- Grant permissions on see execution plan to a login
--
USE [TestDB]
GO
GRANT SNOWPLAN ON DATABASE::TestDB     TO [SomeUser]
GO

 

Login:

  • Logins are also instance-level securables.
--
-- Grant permissions on Logins
--
GRANT IMPERSONATE             ON LOGIN::ReportsLogin                      TO AdhocReports_Login
GO

Server:

  • Permissions at server scope could only be assigned when you are pointed to master database.
--
-- Grant permissions on server level objects
--
GRANT ALTER ANY DATABASE      TO DBA_Group
GRANT SERVER CONTROL          TO DBA_Group
GO

 

Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

Earlier this interesting error occurred in our environment. When we tried to use DAC, we ran into this error:

Dedicated administrator connections are not supported. (ObjectExplorer)

For a few moments it seemed unsettling. If a DAC connection cannot connect so Sql Server, then we are in big trouble. Upon some contemplation (a.k.a. banging head on desk), it became apparent that there is huge silver lining to this. This error is be design.

When you open SSMS, the pop up window that comes up for connection is for Object Explorer, not query window. DAC connections are not allowed for Object Explorer window connections. Hence the error. DAC is supposed to be used only during rare events; For that it is necessary to make sure the connections are not used for purposes other than the absolute intended purpose. Hence DAC prevents inadvertent connection requests to Object Explorer with a safe error.

Use Database Engine Query to open new DAC connection.

Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

Earlier last month, while running server side traces on a lab Sql Server machine, I ran into this interesting error.

Error Details:

Windows error occurred while running SP_TRACE_CREATE. Error = 0x80070005(Access is denied.).
Msg 19062, Level 16, State 1, Procedure sp_trace_create, Line 1
Could not create a trace file.
Trace File Permissions

Trace File Permissions

 
 This is easy to fix. Go to the folder where you are attempting to store the trace files. Open the folder properties and go to Security tab.
Make sure the Sql Server service account is added to the permissions list with necessary read and write permissions.
Hope this helps,

_Sqltimes

Read Full Post »

Quick one today:

Starting Sql Server 2012, looks like there has been a change to restarting options with Sql Server when connected from remote SSMS (a.k.a. when connecting to a Sql Server instance on a lab/prod server using SSMS on your laptop).

SqlAgent Restart is Disabled

SqlAgent Restart is Disabled

Looks like this is part of the new security features. In a typical scenario, you rarely (or almost never) would perform restarts on Sql Server instance or Sql Server Agent on a production instance from your local SSMS. During a production maintenance window, the usual practice is to, log into the server (i.e. RDP into the box) and then use Sql Server Configuration Manager to perform such operation. This sounds like a better/safer practice than performing similar operations from my local SSMS. Though it may seem inconvenient, it is better this way to prevent an inadvertent downtime.

As always, there seem to be some techniques to bypass this limitation. Though there has not been a successful outcome when these techniques were attempted, one of the popular suggestions is to open SSMS using ‘Administrator’ privileges.

Other techniques are listed in the blog here. Please not that I do not recommend attempting any of them.

 
Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

My permissions:

Occasionally, we do want a quick way to check and gather a list of all permissions that my login has. Now there is a simple way:

--
-- Query my permissions on given securable
--
SELECT * FROM fn_my_permissions('SampleTable', 'object')
GO

 

What permissions are allowed?

Similarly, to query all possible permissions that could be given on a securable, there is an equally simpler way:

--
-- List of permissions allowed on each securable
--
SELECT * FROM fn_builtin_permissions('object')
GO
SELECT * FROM fn_builtin_permissions('schema')
GO
Permissions On Securables

Permissions On Securables

Hope this helps,

Read Full Post »