Microsoft – SQL Server – Security – Permissions

Introduction

A big part of one’s life as a DBA is to understand a system’s security apparatus. In some cases, you want to grant very granular permission sets.

Granularity Requirement Permission Version
 Instance Run SQL Server Profile\Capture SQL Server Trace grant alter trace to [Username]; v2005
 Database Capture SQL Server Statement Showplan grant showplan to [database_principal]; v2005
 Instance View Server State grant view server state to [Login]; v2005
 Instance Alter Trace grant alter trace to [Login]; v2005
 Instance View Any definition grant VIEW ANY DEFINITION to [login]; v2005
 Database  View definition grant VIEW DEFINITION to [login]; v2005
Database – msdb Create and Schedule Jobs exec msdb.dbo.sp_addrolemember       N’SQLAgentUserRole’
    , [login-name];
v2005
Database – msdb  Review Job exec msdb.dbo.sp_addrolemember       N’SQLAgentReaderRole’
, [login-name];
v2005
Database – msdb Create new jobs, Review existing jobs, Start and Stop Jobs execmsdb.dbo.sp_addrolemember             N’SQLAgentOperatorRole’
, [login-name]
;
v2005
Database – msdb Review DB Jobs execmsdb.dbo.sp_addrolememberN’TargetServersRole’, [login-name]
;
v2000
 Instance Read error logMsg 229, Level 14, State 5, Procedure xp_readerrorlog, Line 1The EXECUTE permission was denied on the object ‘xp_readerrorlog’, database ‘mssqlsystemresource’, schema ‘sys’. GRANT EXECUTE ON master.dbo.xp_readerrorlog TO [login-name] v2005
 Instance SSIS Packages stored in msdbEnumerate all packages, View all packages, Execute all packages, Export all packages. exec msdb.dbo.sp_addrolemember N’db_ssisadmin’, [login];
 Instance SSIS Packages stored in msdbEnumerate own packages, Enumerate all packages, View own packages, Execute own packages, Export own packages exec msdb.dbo.sp_addrolemember N’db_ssisltduser’, [login];
 Instance SSIS Packages stored in msdbEnumerate all packages, View all packages, Execute all packages, Export all packages exec msdb.dbo.sp_addrolemember N’db_ssisoperator’, [login];
 Instance DBCC TraceOn/DBB TraceOff sysadmin
It does not appear that permission can be delegated

Code – Syntax & Sample

TargetServersRole

Please keep in mind that TargetServersRole only has this functionality in MS SQL Server v2000.

Syntax:

use [msdb]
go

exec sp_adduser N'<DOMAIN-NAME\AD-ACCOUNT>', N'<DOMAIN-NAME\AD-ACCOUNT>'
exec sp_addrolemember N'TargetServersRole', N'<DOMAIN-NAME>\AD-ACCOUNT'
GO

Sample:


use [msdb]
go

exec sp_adduser N'LABDOMAIN\dadeniji', N'LABDOMAIN\dadeniji'
exec sp_addrolemember N'TargetServersRole', N'LANDOMAIN\dadeniji'
GO

Database Engine – DBCC Permissions

DBCC Permissions

Granularity Requirement Permission Version
 Instance DBCC INPUTBUFFER Sysadmin for all sessions
Each user also has permissions to view text in his/her own sessions
N/A

Permissions – DDL – Stored Procedures

To be able to view Stored Procedures, you need to have “View Definition” permissions.Without it, you will have an empty “Stored Procedures” branch.ViewAnyDefinition Once granted, the Stored Procedures in the DB, will be listed:ViewAnyDefinitionGranted

Database Engine – Permission Lister

The other part of the permission puzzle is how to tell who has which permission. And, for that I offer you a nice blog post by Microsoft’s Rick Byham:

Effective Database Engine Permissions
http://social.technet.microsoft.com/wiki/contents/articles/15180.effective-database-engine-permissions.aspx?wa=wsignin1.0&CommentPosted=true#commentmessage

Integration Services

http://technet.microsoft.com/en-us/library/hh213130.aspx
In previous versions of SQL Server, by default when you installed SQL Server all users in the Users group had access to the Integration Services service. When you install the current release of SQL Server, users do not have access to the Integration Services service. The service is secure by default. After SQL Server is installed, the administrator must grant access to the service.To grant access to the Integration Services service

  • Run Dcomcnfg.exe. Dcomcnfg.exe provides a user interface for modifying certain settings in the registry.
  • In the Component Services dialog, expand the Component Services > Computers > My Computer > DCOM Config node.
  • Right-click Microsoft SQL Server Integration Services 11.0, and then click Properties.
  • On the Security tab, click Edit in the Launch and Activation Permissions area.
  • Add users and assign appropriate permissions, and then click Ok.
  • Repeat steps 4 – 5 for Access Permissions.
  • Restart SQL Server Management Studio.
  • Restart the Integration Services Service.

On MS Vista  and later OSes, if the permissions listed above are not granted one will get the error message pasted below:

Connecting to the Integration Services service on the computer “Server” failed with the following error: “Access is denied.”

By default, only administrators have access to the Integration Services service. On Windows Vista and later, the process must be running with administrative privileges in order to connect to the Integration Services service.

References

References – Trace / SQL Profiler

References – ErrorLog

References – ErrorLog – Version

References – ShowPlan

References – Server

References – Definition

References – DBCC

References – DBCC / InputBuffer

References – msdb/packages

References – SQL Server Agent – TargetServersRole

References – SQL Server Agent

References – Integration Services

Leave a Reply

Please log in using one of these methods to post your comment:

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