[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

Deny connsect SQL from Stored Proecdure

Hello
I have a stored procedure the follwoing statement exists in the procedure

DENY CONNECT SQL TO AAAA CASCADE

The problem is i get the error message
Msg 4621, Level 16, State 10, Line 1
Permissions at the server scope can only be granted when the current database is master

I tried to preceed the stamenet
use master
but i got error message
a USE database statement is not allowed in a procedure, function or trigger.

So how can execute from Stored procedure
0
ali_alannah
Asked:
ali_alannah
1 Solution
 
Rainer JeschorCommented:
Hi,
you would have to convert the command in the to a string and run it  with a
execute Your string
see ref here
http://salviashish.wordpress.com/2009/07/06/sql-execute-with-character-string/

Dont forget to set the "use master" at the beginning of the string.
HTH
Rainer
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now