How to encrypt a SP?

Using WITH ENCRYPTION keyword as below:

[cc lang="SQL"]

CREATE PROCEDURE uspPersons WITH ENCRYPTION
AS
SELECT * FROM person.contact
GO

[/cc]

If we try to view using the system stored procedure like sp_helptext it will shout “objects have been encrypted”.

How to decrypt?

Alter the proc without the “WITH ENCRYPTION” key word.

[cc lang="SQL"]

CREATE PROCEDURE uspPersons
AS
SELECT * FROM person.contact
GO

[/cc]

Multiple ways to count the number of records in a table of SQL server:

1. select count(*) or select count(id) or select count (distinct id)  : if we want the exact number but it is an additional overhead as it does table scan or index seek depending on the column chosen.

2. Sysindexes: The columns that need be taken into account are id, indid, rowcnt

indid can take various values as shown below:

0 – no clustered index

1 – CI

indid > 1 and indid < 255 – non CI

255 – text/image datatypes

Query to find the number of rows using sysindexes:

[cc lang=”SQL”]

SELECT so.[name] as [table name]
     , CASE WHEN si.indid between 1 and 254
            THEN si.[name] ELSE NULL END 
            AS [Index Name]
     , si.indid 
     FROM sysindexes si
          INNER JOIN sysobjects so
             ON si.id = so.id
     WHERE si.indid < 2 
       AND so.type = ‘U’ 
       AND so.[name] != ‘dtproperties’
     ORDER BY so.[name]

[/cc]

If the statistics are not updated, above number might not be exact. So update statistics using

[cc lang=”SQL”]

dbcc updateusage

[/cc]

It gives us the ability to refer to the objects in in another schema or database or using linked server to remote server too avoiding the 4 part naming convention.

Roles help in maintaining security in all components of SQL server.

To configure SQL Server job agent fixed server roles, we need to navigate to msdb –> security –> roles –> DB roles.
SQL agent roles:

SQLAgentUserRole : Ability to manage Jobs that they own

SQLAgentReaderRole: Has additional rights than SQLAgentUserRole and also to monitor multiserver jobs

SQLAgentOperatorRole: SQLAgentReaderRole rights in addition to modify operators, proxy and alert. Delete job history and modify the schedule of local jobs.

Most of the information related to SQL Server or DB are stored in the system catalogs like sys tables or information_schema views.

sys.servers – local, remote and linked servers
sp_helpserver – SQL Server metrics
sp_configure – server wide configurations like DOP, processors etc.,
sp_serveroption – Configures server options for remote or linked servers
SELECT SERVERPROPERTY(‘Edition’) – collation, edition, instance name, security configuration, etc

sp_server_info – Approximately 30 connection related parameters primarily for ODBC
SELECT @@SERVERNAME – return the SQL Server instance name
sp_helpsort – System stored procedure to return the sort order and character set for the SQL Server
sp_who2 – active processes
sys.dm_exec_sessions – process (spid) is active, sleeping, etc