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 use
r
to create a stored procedure?
Thanks,
--
Dan D.I ran this query EXEC sp_helprotect NULL, 'public' and saw that public has
the permissions to create Default, create function, create procedure, create
rule, create table, create view, backup database, and backup transaction.
I created a new database and these public did not have these permissions so
I guess public doesn't have them by default.
Is it better to revoke or deny those permissions to public?
Thanks,
--
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 u
ser
> to create a stored procedure?
> Thanks,
> --
> Dan D.|||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 u
ser
> to create a stored procedure?
> Thanks,
> --
> Dan D.|||What does the query 'SELECT USER' return?
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:2E69E961-1C77-4B9D-9962-F4C11B7017EB@.microsoft.com...[vbcol=seagreen]
> 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:
>|||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@.discussions.microsoft.com> wrote in message
> news:2E69E961-1C77-4B9D-9962-F4C11B7017EB@.microsoft.com...
>
>|||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@.discussions.microsoft.com> wrote in message
news:FB897411-12FA-47C0-BDD8-5855475E1A2C@.microsoft.com...[vbcol=seagreen]
> It returns 'dbo'.
> --
> Dan D.
>
> "Dan Guzman" wrote:
>|||The new user that I created in the new database I connected to through QA an
d
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 tha
t
> new user?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:FB897411-12FA-47C0-BDD8-5855475E1A2C@.microsoft.com...
>
>|||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@.discussions.microsoft.com> wrote in message
news:A193F3D7-B34C-49A8-AA8E-2CA851011AFD@.microsoft.com...[vbcol=seagreen]
> 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:
>|||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 t
o
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 puttin
g
> 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 t
he
> database, when that login connects to the database. Please read about logi
ns
> and user names in the Books Online.)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:A193F3D7-B34C-49A8-AA8E-2CA851011AFD@.microsoft.com...
>
>|||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@.discussions.microsoft.com> wrote in message
news:583E27E1-1C79-40D8-A9D2-A22B348D2154@.microsoft.com...[vbcol=seagreen]
> 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:
>
