Home » 2013 » January (Page 2)

Monthly Archives: January 2013

Advertisements

MSSQLFUN

Issue : Today I have read one issue over one forum, One user is having below 3 DB roles on MSDB but whenever user try to run SQL Agent job, it get below error message.

We have Checked that SQL Agent job related DB role is properly given to user. Also, Job is working fine by the use is having sysadmin roles.

· SQLAgentUserRole

· SQLAgentReaderRole

· SQLAgentOperatorRole

Resolution : We have found that someone has deny the execute permissions from SQLAgentUserRole over sp_start_job store procedure in MSDB.

We have Run the below query to check the permissions over sp_start_job store procedure in MSDB.

USE MSDB

GO

SELECT PR.NAME, DP.PERMISSION_NAME, DP.STATE_DESC

FROM MSDB.SYS.DATABASE_PERMISSIONS DP

JOIN MSDB.SYS.OBJECTS O ON DP.MAJOR_ID = O.OBJECT_ID

JOIN MSDB.SYS.DATABASE_PRINCIPALS PR

ON DP.GRANTEE_PRINCIPAL_ID = PR.PRINCIPAL_ID

WHERE O.NAME = ‘SP_START_JOB’

Found that someone has deny the execute permissions from SQLAgentUserRole over sp_start_job store procedure in MSDB.

By running…

View original post 30 more words

Advertisements

The EXECUTE permission was denied on the object ‘sp_start_job’, database ‘msdb’, schema ‘dbo’.


Issue : Today I have read one issue over one forum, One user is having below 3 DB roles on MSDB but whenever user try to run SQL Agent job, it get below error message.

We have Checked that SQL Agent job related DB role is properly given to user. Also, Job is working fine by the use is having sysadmin roles.

· SQLAgentUserRole

· SQLAgentReaderRole

· SQLAgentOperatorRole

Resolution : We have found that someone has deny the execute permissions from SQLAgentUserRole over sp_start_job store procedure in MSDB.

We have Run the below query to check the permissions over sp_start_job store procedure in MSDB.

USE MSDB

GO

SELECT PR.NAME, DP.PERMISSION_NAME, DP.STATE_DESC

FROM MSDB.SYS.DATABASE_PERMISSIONS DP

JOIN MSDB.SYS.OBJECTS O ON DP.MAJOR_ID = O.OBJECT_ID

JOIN MSDB.SYS.DATABASE_PRINCIPALS PR

ON DP.GRANTEE_PRINCIPAL_ID = PR.PRINCIPAL_ID

WHERE O.NAME = ‘SP_START_JOB’

Found that someone has deny the execute permissions from SQLAgentUserRole over sp_start_job store procedure in MSDB.

By running the below query, execute permission has been given back & issue has been resolved.

USE MSDB

GO

GRANT EXECUTE ON SP_START_JOB TO SQLAGENTUSERROLE

Reference : Rohit Garg (http://mssqlfun.com/)

How to move file through FTP by CMD ?


CMD code to upload file direct into root of FTP site of server

@ echo off

REM ******** this batch file is to upload file

echo user username> ftpcmd.dat

echo password>> ftpcmd.dat

echo bin>> ftpcmd.dat

echo lcd D:\Filelocation>>ftpcmd.dat

echo put filename.csv>> ftpcmd.dat

echo quit>> ftpcmd.dat

ftp -n -s:ftpcmd.dat 172.xx.xx.xxx

del ftpcmd.dat

CMD code to upload file direct into sub directory of FTP site of server

@ echo off

REM ******** this batch file is to upload file

echo user username> ftpcmd.dat

echo password>> ftpcmd.dat

echo bin>> ftpcmd.dat

echo lcd D:\Filelocation>>ftpcmd.dat

echo put filename.csv .\Subdirectory\ filename.csv >> ftpcmd.dat

echo quit>> ftpcmd.dat

ftp -n -s:ftpcmd.dat 172.xx.xx.xxx

del ftpcmd.dat

Reference : Rohit Garg (http://mssqlfun.com/)

What happen, If SQL Server new instance try to use exitsing named instance name?


We all know that SQL Server can have 1 default instance & many named instance.

But I want to check, How SQL Server Setup behave, If I tries to use instance name that is already being used by some existing SQL server on my system.

I am already having 2 instances running on my system, 1 SQL Server 2008 & 1 SQL Server 2012.

I must say, SQL Server setup is too cleaver. It shows me that instance that use the name (passed by me) & show me a SQL server version of that instance as well.

In same way, It works for both default & Named instance.

1) While installing 3rd instance of SQL Server 2005, I pass the instance name used by SQL Server 2008.

2) While installing 3rd instance of SQL Server 2005, I pass the instance name used by SQL Server 2012.

MSSQLFUN – Year 2012 Review


The WordPress.com stats helper monkeys prepared a 2012 annual report for this blog.

Here’s an excerpt:

600 people reached the top of Mt. Everest in 2012. This blog got about 2,000 views in 2012. If every person who reached the top of Mt. Everest viewed this blog, it would have taken 3 years to get that many views.

Click here to see the complete report.