Re: public role question

Who is the owner of the procedure when user 'dantest' runs the following
script? Perhaps that will provide a clue.

CREATE PROC TestProc
AS SET
GO

I can't think of another way your 'dantest' user can have CREATE PROCEDURE
permissions. Try the following controlled test to see if a newly created
login/user can create procedures:

EXEC sp_addlogin 'dantest2', 'somepassword', 'MyDatabase'
GO
USE MyDatabase
GO
EXEC sp_adduser 'dantest2'
GO
SETUSER 'dantest2'
GO
CREATE PROC TestProc
AS SET
GO
SETUSER
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Dan D." <DanD@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:473B95DA-2BFB-4832-B2E1-21333A73F74A@xxxxxxxxxxxxxxxx
I logged on to the database through QA as dantest. I ran sp_helprolemember
'db_owner' and one row was returned with 'db_owner' in the dbrole column.
I
ran sp_helprolemember 'db_ddladmin' and no rows were returned. I ran
sp_helprotect 'CREATE PROCEDURE' and "There are no matching rows on which
to
report." was returned.
--
Dan D.


"Dan Guzman" wrote:

Sorry I didn't make my instructions clearer. A common cause of elevated
permissions is that users are members of the 'BUILTIN\Administrators'
Windows group and thereby get sysadmin permissions. User 'dbo' would be
returned in that case.

Since 'SELECT USER' returns 'dantest' instead of'dbo', we know 'dantest'
is
not a sysadmin role member and is not the database owner. That means one
of
the following must be true for the user to create procs:

1) The user is a member of the db_owner role. You can check this with
sp_helprolemember 'db_owner'

2) The user is a member of the db_ddladmin role. You can check this with
sp_helprolemember 'db_ddladmin'

3) The user (or a role the member belongs to) was granted CREATE
PROCEDURE
permissions. You can check this with sp_helprotect 'CREATE PROCEDURE'


--
Hope this helps.

Dan Guzman
SQL Server MVP

"Dan D." <DanD@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:583E27E1-1C79-40D8-A9D2-A22B348D2154@xxxxxxxxxxxxxxxx
I'm sorry. I didn't understand what Dan was asking me to do. If I log
in
through QA using the new user and run "select user" I get "dantest"
which
is
the new user. Then I am able to create a stored procedure. If I run
'EXEC
sp_helprotect NULL, 'dantest'', I get "There are no matching rows on
which
to
report.". If I run EXEC sp_helprotect NULL, 'public', I get a list of
18
system tables with select permissions.
--
Dan D.


"Kalen Delaney" wrote:

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
    ... 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: 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 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
    ... 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)