Re: public role question

If 'select user' is returning DBO, it means you are connecting AS DBO, and
not as your new user.

You ARE the special DBO user, and not the new user, which is why you have
all these permissions. You are not connecting as your new user and not
testing the permissions the new user has.

So again, please tell us exactly HOW you are trying to connect as the new
user. If you are connecting through QA, please tell us what you are putting
in every box in the connection dialog.

(Telling you that dbo 'owns' the database would be meaningless. DBO is a
user name. It is the name always given to whatever login is the owner of the
database, when that login connects to the database. Please read about logins
and user names in the Books Online.)

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


"Dan D." <DanD@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A193F3D7-B34C-49A8-AA8E-2CA851011AFD@xxxxxxxxxxxxxxxx
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
    ... 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)
  • 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
    ... I logged on to the database through QA as dantest. ... "Dan Guzman" wrote: ... Windows group and thereby get sysadmin permissions. ... Then I am able to create a stored procedure. ...
    (microsoft.public.sqlserver.security)
  • Re: public role question
    ... Who is the owner of the database? ... "Dan Guzman" wrote: ... Windows group and thereby get sysadmin permissions. ... Then I am able to create a stored procedure. ...
    (microsoft.public.sqlserver.security)
  • Re: public role question
    ... database?), I get 'dbo'. ... procedure if public doesn't have permissions and the new user is only in the ... Are you saying that the permissions to create the stored procedure are ... Dan D. ...
    (microsoft.public.sqlserver.security)