Re: public role question

The new user that I created in the new database I connected to through QA and
was able to create a stored procedure. If I run EXEC sp_helprotect NULL,
'public', the only permissions that public has is for "select" to 18 system
tables. If I run "select user" (and what does this tell me - who owns the
database?), I get 'dbo'. So I'm wondering how I'm able to create a stored
procedure if public doesn't have permissions and the new user is only in the
public role with no other permissions?

Are you saying that the permissions to create the stored procedure are
coming from dbo so I need to revoke them from 'dbo'?

The ultimate objective is to be able to create a login/user that a web app
will use and the user should only have permissions for "select" to the views
and "execute" on the stored procedures with no permissions on the underlying
tables. Would I need to revoke insert, update, and delete permissions from
public in this case?

Thanks for you help,
--
Dan D.


"Kalen Delaney" wrote:

Hi Dan

The user dbo is a privileged user and can create tables and procs, execute
procs, and access data in all the tables. You should try to revoke
permissions from the user dbo.

You have said you created a new user. How are you trying to connect as that
new user?

--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com


"Dan D." <DanD@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FB897411-12FA-47C0-BDD8-5855475E1A2C@xxxxxxxxxxxxxxxx
It returns 'dbo'.
--
Dan D.


"Dan Guzman" wrote:

What does the query 'SELECT USER' return?

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Dan D." <DanD@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2E69E961-1C77-4B9D-9962-F4C11B7017EB@xxxxxxxxxxxxxxxx
In the new database that I created, I was able to create a stored
procedure.
I ran EXEC sp_helprotect NULL, 'public' but I don't see the "create
procedure" permisson listed under the action column. How am I able to
create
a stored procedure with a user that is only a member of public and has
no
other permissions?
--
Dan D.


"Dan D." wrote:

Using SS2000 SP4. I'm trying to lock down the database as much as
possible. I
created a new sql server login and gave the user no permissions other
than
public role.

Why am I able to create a stored procedure with the new user. I
thought
Public had select, insert, update, delete permissions. Does that allow
a
user
to create a stored procedure?

Thanks,
--
Dan D.






.

Relevant Pages

  • Re: public role question
    ... If 'select user' is returning DBO, it means you are connecting AS DBO, and ... testing the permissions the new user has. ... Are you saying that the permissions to create the stored procedure are ... Dan D. ...
    (microsoft.public.sqlserver.security)
  • Re: public role question
    ... Dan, I'm sorry but after I responded to Kalen's email I deleted the test ... Somehow the user 'dantest' was the owner of the database so I guess ... I did create a second user with only the public role for permissions and was ... Then I am able to create a stored procedure. ...
    (microsoft.public.sqlserver.security)
  • Re: public role question
    ... The user dbo is a privileged user and can create tables and procs, ... permissions from the user dbo. ... "Dan Guzman" wrote: ... Why am I able to create a stored procedure with the new user. ...
    (microsoft.public.sqlserver.security)
  • Re: Deny access to all users (including Administrator and DomainAd
    ... permissions on stored procedure override ... applictaion's user account permissions to the stored procedure which will ... Will try to deny access through the query for SQL Server 2005 and 2000. ... Remove everyone that you don't want rom an Administrator Groups ...
    (microsoft.public.sqlserver.security)
  • Re: public role question
    ... I didn't understand what Dan was asking me to do. ... Then I am able to create a stored procedure. ... system tables with select permissions. ... You ARE the special DBO user, and not the new user, which is why you have ...
    (microsoft.public.sqlserver.security)