Deny connsect SQL from Stored Proecdure

Posted on 2012-08-26
Last Modified: 2012-08-26
I have a stored procedure the follwoing statement exists in the procedure


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
Question by:ali_alannah
    1 Comment
    LVL 44

    Accepted Solution

    you would have to convert the command in the to a string and run it  with a
    execute Your string
    see ref here

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

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    This video discusses moving either the default database or any database to a new volume.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now