Re: Stored procedures security

From: Nikolay Petrov (johntup2_nospam__at_mail.bg)
Date: 10/27/04 

Date: Wed, 27 Oct 2004 17:44:17 +0300

Jacco, this statment will grant execute permission to non MS strored
procedures, right?

"Jacco Schalkwijk" <jacco.please.reply@to.newsgroups.mvps.org.invalid> wrote
in message news:uIqopnCvEHA.3908@TK2MSFTNGP12.phx.gbl...
> SELECT 'GRANT EXEC ON [' + routine_name + '] TO Nikolay'
> FROM information_schema.routines
> WHERE routine_type = 'PROCEDURE'
> AND OBJECTPROPERTY(OBJECT_ID(routine_name), 'IsMSShipped') = 0
>
> Run the above in Query Analyzer with results to text, and run the
> resulting script. As long as you haven't granted any permissions to the
> public role those are the only permissions your user will have. IF there
> are, you can use the above with DENY and information_schema.tables etc.
>
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
>
> "Nikolay Petrov" <johntup2_nospam_@mail.bg> wrote in message
> news:OF4$ISCvEHA.1308@TK2MSFTNGP09.phx.gbl...
>>I have db where I want a specified user to be allowed only to run stored
>>procedures.
>> How to grant permissions to these stored procedures and deny access to
>> all other functions?
>>
>> TIA
>>
>
>

Relevant Pages

  • Re: Stored procedures security
    ... Jacco, this statment will grant execute permission to non MS strored ... > public role those are the only permissions your user will have. ... >> How to grant permissions to these stored procedures and deny access to ...
    (microsoft.public.sqlserver.security)
  • Re: Stored procedures security
    ... Jacco, this statment will grant execute permission to non MS strored ... > public role those are the only permissions your user will have. ... >> How to grant permissions to these stored procedures and deny access to ...
    (microsoft.public.sqlserver.server)
  • Re: Help understanding Stored proc Level Secuirty?
    ... Jasper Smith (SQL Server MVP) ... I set permissions to my Stored Procedures, ... Access to stored procs fail ...
    (microsoft.public.sqlserver.security)
  • Re: Securing Stored Procedures from being seen
    ... PERMISSIONS works for the current user, whoever he may be, and however his rights were granted. ... username, but in SQL Server, there are too many users to map an individuals ... granting the group execute permissions. ... see the names of any stored procedures to which he has no rights. ...
    (microsoft.public.sqlserver.security)
  • Re: Newbie on permissions: ADO.NET, C++.NET, SQL SERVER 2005 EXPRESS, Visual Studio 2005
    ... Permissions are per logins and users. ... permissions can be granted through stored procedures, ... an extent a trivial question since I can do programming in VS as ... "Administrator" (it's annoying to switch users using Windows XP Pro, ...
    (comp.databases.ms-sqlserver)
Quantcast