Re: public role question

What database are you in? Who is the owner of the database?


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


"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
    ... 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: Select Permission Denied On Object
    ... The main consideration with cross database chaining is that the security implications aren't obvious. ... On the other hand, if data are not sensitive and direct selects are no big deal, go with select permissions. ... > I have a stored procedure in one database, ...
    (microsoft.public.sqlserver.security)
  • Re: Select Permission Denied On Object
    ... If I were to implement db chaining, what would be a good generic ... table in another database? ... Permissions on indirectly referenced objects are not needed as long as the ... I have a stored procedure in one database, ...
    (microsoft.public.sqlserver.security)
  • Re: public role question
    ... I'm logged in to the 'dantest' database as user 'dantest'. ... As a rule if I want to restrict permissions as long as I don't explicitly ... Dan D. ...
    (microsoft.public.sqlserver.security)