Feeds:
Posts
Comments

Archive for December, 2015

Quick one today

A few months ago, in our lab we tried to enable SSH access to our ESXi hosts and these are the steps we followed.

  1. First, if you have access to the ESXi console, hit F2 and log in.
  2. From the System Customization screen, go to ‘Troubleshooting Options’, and hit <Enter>.
  3. In the ‘Troubleshooting Mode Options’ screen, you see a bunch of toggle options.
  4. Go to ‘Enable SSH’ and hit <Enter> (when you hit <Enter>, the option toggles to ‘Disable SSH’).
  5. Exit from each screen.

 

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
&amp;amp;lt;/pre&amp;amp;gt;&amp;amp;lt;pre&amp;amp;gt;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 »

A few months ago, there was an interesting conversation that uncovered this recommendation for maximum & minimum server memory setting for VLDB instances. Memory is a crucial to any database servers; And it becomes more important for VLDBs. So it is important that we achieve optimal memory settings. As with anything else, the correct setting depends on several factors and it varies from instance to instance. Here we’ll cover general recommendation that could be used as baseline.

Usually database servers are dedicated machines. So, the only major application running on the OS is the Sql Server instance. So, we need to carve out memory for OS and Sql in a way that both run efficiently. For database server instances on VM or shared machines, memory configuration gets a bit more complicated; but this could used as a general guideline to start with and apply more relevant factors on top of it.

First, query your current settings:

--
--  Query the current memory allocation setting
--
SELECT *
FROM sys.configurations
WHERE name LIKE '%memory (MB)%'
GO

For VLDB’s, generally the size of memory is larger than other database servers. As the server memory increases, the configuration changes. The general point is that we need to allocate as much memory to the Sql instance as possible. But this should not be at the cost of OS itself. So, we need to strike a right balance so the OS could run efficiently (without paging) while allocating as much memory to Sql instance as possible. To achieve this, we use the two settings available for each instance.

  • min server memory (MB)
  • max server memory (MB)
Total Server Memory For OS Minimum Memory for SQL Maximum Memory for SQL
1 – 4 GB 2 GB 2 GB 2 GB
4 – 8 GB 3 GB 3 GB 5 GB
8 – 12 GB 4 GB 3 GB 8 GB
12 – 16 GB 5 GB 3 GB 11 GB
16 – 24 GB 6 GB 7 GB 18 GB
24 – 32 GB 7 GB 7 GB 25 GB
32 – 40 GB 8 GB 7 GB 32 GB
40 – 48 GB 9 GB 7 GB 39 GB
48 – 56 GB 10 GB 7 GB 46 GB
56 – 64 GB 11 GB 7 GB 53 GB

For VLDBs with more than 64 GB, we need to consider more factors on top of the guidelines above. We’ll dive into it in a future post.

--
-- Configure Memory Settings 
--
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

EXEC sp_configure 'max server memory (MB)', 5120
RECONFIGURE
GO

EXEC sp_configure 'show advanced options', 0
RECONFIGURE
GO

Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

A while ago, we had a post on how to set a database in Single User mode to perform some operations. Now, we could look at how to set a database back to multi user mode.

--
-- Set a database in multi user
--
ALTER DATABASE DBADB
SET MULTI_USER
GO

It is simple and runs quickly.

Hope this helps,
_Sqltimes

Read Full Post »