Grant Access SQL Server Agent using tSQL/SSMS, SQL Server

When you need to grant the user to access (read/manage) "SQL Server Agent", you can do it using either using SSMS or tSQL.

There are three (3) fixed database roles available under "msdb" database to access SQLServerAgent. Roles are as below...

• SQLAgentOperatorRole: This is the most privileged of the SQL Server Agent fixed database roles. Members can create/delete/modify any job & schedules (own by any user). Also, Members of this role can view properties for operators and proxies, and enumerate available proxies and alerts on the server.
• SQLAgentReaderRole: Members have permission to view the properties of any job & schedules (own by any user). Members cannot change job ownership which is own by others to gain access.
• SQLAgentUserRole: Members have permissions on only local jobs and schedules that they own. They can also view the operators. No permission to view the Proxy node BUT in Job  Step they can see to use only.

For more details about the Roles, please visit below link ...
https://docs.microsoft.com/en-us/sql/ssms/agent/sql-server-agent-fixed-database-roles

Now, let's grant the appropriate role to a user. There are two ways you can do it.

1. Using SSMS: Please follow below steps to grant a user to an SQLAgentRole



SSMS - Grant SQL Server Agent Role

1. Expand the Database Server Node.
2. Expand Security Node.
3. Select the User where you want to grant access. Right click on it and go to Properties
4. In Properties window, navigate "User Mapping" from the left pane.
5. From the right pane, select/tick on "msdb" database.
6. In the bottom pane of the same window, select appropriate fixed database role [discussed above].
7. All set, now click OK and check :)
2. Using tSQL: Using the below script you can grant any role to a user...
1. Change the user name and role name accordingly

2. USE [msdb] GO CREATE USER [MEDAVANTE\testUser] FOR LOGIN [MEDAVANTE\testUser] GO USE [msdb] GO ALTER ROLE [SQLAgentReaderRole] ADD MEMBER [MEDAVANTE\testUser] GO

Hope this post will help you to grant SQLServerAgent fixed role access to any user.

How to find the SQL Server Instance name

First, check that the SQL Server service is installed in the machine/server bu using "services.msc".

For that, press WindowsKey+R to get the Run dialog. Then type "Services.msc". Then find the service "SQL Server ...".

If you get the SQL Server service in the service list, that means SQL Server is installed in this server/PC/Machine.

Now let's find the instance name...

First Option [will work for all - Recomendade]
go to start menu and find "SQL Server Configuration Manager". open it.
point to the "SQL Server Services".
from the right pane find the service "SQL Server (instance name)" [as you get in the service.msc list]
now Right click on it and go to "Properties".
now click on "Server" Tab.
you will get all the information here .. look at the below image.

Configuration Manager

Second Option [sqlcmd]
Open "Command Prompt" [ press "WindowsKey+R", then type "cmd" on run dialog]
in the command prompt console -> type "sqlcmd -L"
you will get the server name and the instance name most luckily
check below image ...

sqlcmd -L

SSIS Connection Problem, "The specified service does not exist as an installed service."

If you get the error "The specified service does not exist as an installed service.", please dont be panic!

This is a very common issue. Follow below steps to ride on it.

1. First, check the SSIS is installed in the server. for that go to services.msc and try to find the service "SQL Server Integration Services xx" [xx can be 12/13/14 ..]. If there is no service like this, means no SSIS has been installed in this server. OR if you find the service, please follow below steps.

2. This is must, use the same version of SSMS to access the SSIS. If you have the DB permission, then check the DB version. most luckily SSIS and DB version is same. To be sure the version number, I always pick the service number [for my case it was "SQL Server Integration Services 13"]. 13 means SQL Server 16!

3. Once you get the version number of SSIS/DB. Download the SSMS for the same version from here. after install, try to connect to SSIS, I hope you will win!


Also, you can download the Free Developer Edition of SQL Server (SSIS Included) from here .. https://www.microsoft.com/en-us/sql-server/sql-server-downloads 

Search and Restore .bak File [BULK RESTORE], SQL Server

You can say this process as BULK Restore of .bak file.

Using the below script we can search and restore bak files; Just we need to follow some pattern.

Make sure the .bak file names start with DB name and some pattern.

Below script will search the bak file from disk/provided path and restore the file.

Follow the below steps to prepare the environment.

1. Create a Table [dbo].[dbNames] in [master] database.

CREATE TABLE [dbo].[dbNames](  [ID] [int] IDENTITY(1,1) NOT NULL,  [DBNAME] [nvarchar](max) NULL,  [DBFILENAME] [nvarchar](max) NULL,  [DBFILELOCATION] [nvarchar](max) NULL,  [Comment] [nvarchar](500) NULL,  CONSTRAINT [PK_dbNames] PRIMARY KEY CLUSTERED ([ID] ASC)  )

2. Insert Database names which need to restore and leave [Comment] column blank

INSERT INTO [dbo].[dbNames] ([DBNAME], [DBFILENAME], [DBFILELOCATION]) values ('Test_Database','FileName','FileLocation') Example SQL select d.name,m.name,m.physical_name from sys.databases d JOIN sys.master_files m ON d.database_id = m.database_id where d.name not in ( 'master','tempdb','model','msdb','DBAdmin')

3. Make sure all .bak files name start with the Database name and some static words ie. "_Backup_2019"

Test_Database_Backup_20190131_01.bak
Test_Database2_Backup_20190131_01.bak

4. Now Adjust the below Script and Run. wow ... all databases are ready to use!!

declare @dbName varchar(MAX) ='' declare @fileName varchar(MAX) declare dbNamecursor cursor for select distinct DBNAME from dbNames Where Comment is null DECLARE @DataPath nvarchar(500), @DBFilePath nvarchar(500), @DBLogPath nvarchar(500) SET @DataPath = '<BAK file location>' SET @DBFilePath = '<DB file (.mdf) location>\' SET @DBLogPath = '<DB LOG file (.ldf) location>\' DECLARE @DirTree TABLE (subdirectory nvarchar(255), depth INT,IS_FILE bit) INSERT INTO @DirTree(subdirectory, depth,IS_FILE) EXEC master.sys.xp_dirtree @DataPath,0,1 declare @createSQL varchar(MAX) ='' declare @createMOVESQL varchar(MAX) ='' OPEN dbNamecursor FETCH NEXT FROM dbNamecursor INTO @dbName WHILE @@FETCH_STATUS = 0 BEGIN set @fileName = null select @fileName=a.subdirectory from @DirTree a where a.subdirectory like @dbName+'_Backup_FULL_20190924%.bak' SET @createSQL = '' SET @createMOVESQL='' if @fileName is not null begin print char(10)+'@dbName = '+@dbName+' --> @fileName = '+@fileName set @createSQL = 'restore database ['+@dbName+ '] from disk = '''+@DataPath+'\'+@fileName+''' ' select @createMOVESQL = @createMOVESQL + ' , MOVE '''+s.DBFILENAME+''' TO '''+case when s.DBFILELOCATION like '%ldf' then @DBLogPath+DBNAME+'_log' else @DBFilePath+DBNAME end+'_'+ cast ( ROW_NUMBER() over (order by s.DBFILENAME) as varchar) +right(s.DBFILELOCATION,4)+''' ' from dbNames s where  DBNAME=@dbName set @createSQL= @createSQL+ ' WITH '+right(@createMOVESQL,len(@createMOVESQL)-2) print @createSQL exec(@createSQL) update dbNames set Comment='Done' where DBNAME=@dbName end else begin print char(10)+'@dbName = '+@dbName+' --> @fileName = NOT FOUND' update dbNames set Comment='FILE NOT FOUND' where DBNAME=@dbName end FETCH NEXT FROM dbNamecursor INTO @dbName END CLOSE dbNamecursor; DEALLOCATE dbNamecursor;

Control and Loop Statements in R

Control and Loop Statements in R.

There are 2 types of Control/Conditional statements and 3 types of Loop available in R. see below

See Also: Introduction to R/ Basic functions of R

Conditional Statements
1. if ... else
2. ifelse() Function

Loop Statements
1. for loop
2. while loop
3. repeat loop

Related Statements/Topics
1. break
2. next
3. logical expression

Conditional Statements
1. if...else Like other programming languages, the if...else statement is the same in R

NOTE: else if/ else keyword should be placed in the next of closing } of the earlier block.

if(logic_expression) {
stmt.
} else if (logic_expression) {
stmt.
} else {
stmt
}

Example 1
x <- 5
if(x>10) {
print("grater than 10")
}

Example 2
x <- 5
if(x>10) {
print("grater than 10")
} else {
print("less than 10")
}

Example 3
x <- 5
if(x>10) {
print("grater than 10")
} else if (x>5 & x<10) {
print("less than 10 BUT grater than 5")
} else {
print("less than 5")
}

2. ifelse() is a function to use with vector AND the return would be a vector also.

Example 1
x = 1:10
ifelse( x %% 2 == 0 , "even", "odd")
OUTPUT: [1] "odd"  "even" "odd"  "even" "odd"  "even" "odd"  "even" "odd"  "even"

Loop Statements
1. for loop: the basic is the same as other languages BUT the syntex is different

for ( val in vector/sequence )
{
    statement
}

Example 1
v = c(1,2,3,4,5)
for ( x in v )
{
    print (x)
}

Example 2: Fibonacci Series upto 10th number
f = c(1,1)
for (x in 1:8) {
f <- c(f, sum( tail(f,2) ) )
}
print(f)
OUTPUT: [1]   1   1   2   3   5   8  13  21  34  55  89 144

Example 3: Nested For Loop - a simple pyramid of numbers
for(x in 1:9){
p = NULL
for(y in 1:x){
p = c(p, y)
}
print(p)
}
OUTPUT:
[1] 1
[1] 1 2
[1] 1 2 3
[1] 1 2 3 4
[1] 1 2 3 4 5
[1] 1 2 3 4 5 6
[1] 1 2 3 4 5 6 7
[1] 1 2 3 4 5 6 7 8
[1] 1 2 3 4 5 6 7 8 9

2. while loop: basic is also same for this one.

while( logic_expression )
{
    statement
}

Example 1
x=1
y=10
while(x<y)
{
  print(x)
  x = x + 1
}

Example 2 : Create a vector with the sum of current value from the first value [ x=1⅀x=n ].
v=1:10
x=1
sumV = NULL
while ( x <= length(v) )
{
sumV = c(sumV , sum( head(v,x) ))
x = x + 1
}
print(v)
print(sumV)

OUTPUT:

v ==>        [1]  1  2  3  4  5  6  7  8  9 10

sumV ==> [1]  1  3  6 10 15 21 28 36 45 55

3. repeat loop: it just repeats the block until finding a break statement.

repeat {
statement
}

Example 1:
r = 0
count = 1
repeat {
r = c(r,count)
if(count == 5) { break }
count = count + 1
}
print(r)
OUTPUT: [1] 0 1 2 3 4 5

Related Statements/Topics
1. break: this is like other languages. just break a loop to continue and goto the end of the loop.
2. next: this is like continue of other languages. just continue the next iteration by skipping the following statements.
Example 1:
r=NULL
for (x in 1:20) {
if(x == 5) { next}
  if(x == 10) { break}
r = c(r,x)
count = count + 1
}
print(r)
OUTPUT:
In output 5 is missing and loop stopes at 10!
[1] 1 2 3 4 6 7 8 9

3. logical expression: this is also like other languages.
R supports the combination of logical operators..
x > 2 & y <5
x > 2 && y <5
x > 2 | y <5
x > 2 || y <5
for vector:
xV  & yV ==> will check all the elements and return a logical vector
xV  && yV ==> will check the first elements only and return a logical value
xV  || yV ==> will check the first elements only and return a logical value
all logical operators:
&, &&, | ,||, ! (NOT)