db_owner - Unauthorized Size increase

Hello There,
I have a simple questions.
How can i restrict the maximum size of the database for a db_owner?
ALTER DATABASE permissions default to members of the sysadmin and
dbcreator fixed server roles, and to members of the db_owner fixed
database roles. These permissions are not transferable.
They can do this:
This example creates a database and alters it to add a new 5-MB data
file.
USE master
ALTER DATABASE Test1
ADD FILE
(
NAME = Test1dat2,
FILENAME = 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\t1dat2.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)
GO
What i want is to provide the db_owner but we need to restrict the
maxSize of the database.
Sincerely,
Yusbel GarciaIf somebody is db_owner, they can do whatever they like, within
the databases that they own.
Your best bet, which as always is the one involving the most work,
is to look at what functionality your users "must" have, in order for
them to perform their functions.
As always, if you leave something available for someone to use,
they'll eventually use it.
"Yusbel Garcia" <yusbel@.ISQsolutions.com> wrote in message
news:769e3cd4.0308121023.6b555837@.posting.google.com...
> Hello There,
> I have a simple questions.
> How can i restrict the maximum size of the database for a db_owner?
> ALTER DATABASE permissions default to members of the sysadmin and
> dbcreator fixed server roles, and to members of the db_owner fixed
> database roles. These permissions are not transferable.
> They can do this:
> This example creates a database and alters it to add a new 5-MB data
> file.
> USE master
> ALTER DATABASE Test1
> ADD FILE
> (
> NAME = Test1dat2,
> FILENAME = 'c:\Program Files\Microsoft SQL
> Server\MSSQL\Data\t1dat2.ndf',
> SIZE = 5MB,
> MAXSIZE = 100MB,
> FILEGROWTH = 5MB
> )
> GO
> What i want is to provide the db_owner but we need to restrict the
> maxSize of the database.
>
> Sincerely,
> Yusbel Garcia|||Thank for your answer, i believe that microsoft it should have a solutions
to revoke the alter database permission for a db_owner database exist some
hosting company that are providing the db_owner permission and they restrict
the maxSize of a database unles they lie, and i don't believe they use the
windows quota to restrict the maxSize Database.
Regards,
Yusbel Garcia
"Stressed" <k@.c.co.uk> wrote in message
news:eMV%23FuaYDHA.2448@.TK2MSFTNGP09.phx.gbl...
> If somebody is db_owner, they can do whatever they like, within
> the databases that they own.
> Your best bet, which as always is the one involving the most work,
> is to look at what functionality your users "must" have, in order for
> them to perform their functions.
> As always, if you leave something available for someone to use,
> they'll eventually use it.
>
> "Yusbel Garcia" <yusbel@.ISQsolutions.com> wrote in message
> news:769e3cd4.0308121023.6b555837@.posting.google.com...
> > Hello There,
> >
> > I have a simple questions.
> >
> > How can i restrict the maximum size of the database for a db_owner?
> >
> > ALTER DATABASE permissions default to members of the sysadmin and
> > dbcreator fixed server roles, and to members of the db_owner fixed
> > database roles. These permissions are not transferable.
> >
> > They can do this:
> >
> > This example creates a database and alters it to add a new 5-MB data
> > file.
> >
> > USE master
> > ALTER DATABASE Test1
> > ADD FILE
> > (
> > NAME = Test1dat2,
> > FILENAME = 'c:\Program Files\Microsoft SQL
> > Server\MSSQL\Data\t1dat2.ndf',
> > SIZE = 5MB,
> > MAXSIZE = 100MB,
> > FILEGROWTH = 5MB
> > )
> > GO
> >
> > What i want is to provide the db_owner but we need to restrict the
> > maxSize of the database.
> >
> >
> > Sincerely,
> > Yusbel Garcia
>

db_datareader & Execute permissions on procs

I have a group of users that I have given db_datareader permissions to in an SQL Server 2000 database. I am also creating web pages on an intranet site that pulls data from the table. If I just use a select statement to pull the data from the table, the users dont have a problem. If I use a stored procedure with the exact same sql statement, the users get an error until I grant them execute permissions on the stored procedure.

I have heard that store procedures is the best way to handle data operations but having to make sure I assign execute permissions every time I create a stored procedures can be a pain. The only way I know of to make sure that they had permissions would be to make them a member of db_Owner which is definitely not an option.

Is this just the way it is, or is there some way to automatically grant them execute permissions on stored procedures that are nothing more than select statements and dont violate db_datareader permissions?I'm afraid that is the point of procs (from a security pov). You can allow execute on the sproc whilst not allowing them to access the underlying tables. Such granularity means you have to explicitly grant execute permissions on the stored procedures for the users to be able to use them.|||Hi there,

Yep as pootle says, you need to add exec permissions to SPs regardless of whether they have datareader or datawriter.
If you're not doing it already I would set up and NT group or custom database role then you wont need to add the permissions to each user all the time, just to the group or groups|||Alternatively, if you are using SQL 2005, you can grant SCHEMA permissions to a user or role.

grant execute on schema ::dbo to [public]

However, more granular permissions are considered at least a better, if not best, practice.|||thanks to all of you for your replies.sql

Db Users Mix up

Hello:
I am having a problem understanding the db users.
I am working on a DNN site, and having troubles with db users permissions.

I want to know what is the DBO?
I have a user in my database, that is assigned "public".

In my application usually, what should i do:
1- Login with the public user, create tables, views, sp, add data ...
2- Use the dbo always?

In my application, I will be using the public user in the connections string.

Can you help please.

DBO is the db owner.

In DNN, the user should have DBO privileges in order to create/delete/move data.

HTH

|||Thanks, but your answer is not well explained.
Can you explain to me more please? I need more information to understand well.

thanks|||

There are two permissions in SQL Server, the Server permissions in the Security section of Enterprise Manager and the database permissions in the database. What you want to know is roles because DBO is database owner role which is assigned to the owner of the database and the server System Admin is also DBO by default. Then there is public role which every user of the SQL Server belong and cannot be dropped. Try the link below for more info. Hope this helps.

http://www.databasejournal.com/features/mssql/article.php/1441261

DB Security vs. Application

We are rolling out mssqlxpress to our development staff and would prefer to alter their permissions so that they can only make changes using mssqlxpress (since it integrates nicely with VSS).

So are choices are to uninstall SQL Enterprise Manager and SQL Query Analyzer, or more preferably, alter their NT Permissions so that they do not have the capability to change anything in the DB's.

My concern is that it seems we can only set their permissions from DB level and not the application, does that sound correct? Has anyone else had to deal with this predicament and is there an alternative to uninstalling the SQL Client tools?Look into Application Roles topic in BOL, maybe you can specify this source control app as the only app with required permissions, and modify your connection object within the app to use this role. At the same time you revoke all permissions from all individual users in question.

DB Roles

Is there a way to copy a DB Role to a another DB and keep all the
Permissions assign to the role?You sure can script database roles from Enterprise Manager, by right
clicking on the database, and selecting All Tasks -> Generate SQL Scripts.
In the options tab, choose database roles.
I don't think this option scripts the permissions assigned to the roles. I
haven't tried it myself.
But for my database roles, I always create a script that creates the role,
and assigns the permissions. If I need to replicate this role on some other
database, I simply get the latest version of this script from VSS and run it
against that database.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Yovan Fernandez" <yfernandez@.sai-inc.com> wrote in message
news:t_08b.1183$ev2.814323@.newssrv26.news.prodigy.com...
Is there a way to copy a DB Role to a another DB and keep all the
Permissions assign to the role?

db permissions in EM

i've got a user that has permissions to read and write in two
databases. that user has enterprise manager installed and uses it (i
know he shouldn't really have it, but that's another topic). he can see
all of the databases on the server. is there any way to make EM only
show him the two databases which he can access?
ch
Unfortunatly,NO
"ch" <ch@.dontemailme.com> wrote in message
news:41862283.D2817F00@.dontemailme.com...
> i've got a user that has permissions to read and write in two
> databases. that user has enterprise manager installed and uses it (i
> know he shouldn't really have it, but that's another topic). he can see
> all of the databases on the server. is there any way to make EM only
> show him the two databases which he can access?
|||i think i figured out how to do it.
a few changes to master.dbo.sp_MSdbuseraccess
Uri Dimant wrote:[vbcol=seagreen]
> ch
> Unfortunatly,NO
> "ch" <ch@.dontemailme.com> wrote in message
> news:41862283.D2817F00@.dontemailme.com...
|||Which means you no longer have a supported SQL server installation. Be careful when modifying system
objects. Not supported.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ch" <ch@.dontemailme.com> wrote in message news:41862EC4.7EF0C91A@.dontemailme.com...
> i think i figured out how to do it.
> a few changes to master.dbo.sp_MSdbuseraccess
>
|||ch
You can hide a system databases from seeing them with EM , but user
databases you cannot.
"ch" <ch@.dontemailme.com> wrote in message
news:41862EC4.7EF0C91A@.dontemailme.com...[vbcol=seagreen]
> i think i figured out how to do it.
> a few changes to master.dbo.sp_MSdbuseraccess
>
> Uri Dimant wrote:
see[vbcol=seagreen]
|||this is sort of goofy.
query analyzer uses master.dbo.sp_MShasdbaccess intead of
master.dbo.sp_MSdbuseraccess.
sp_MShasdbaccess only returns the databases that the user can get into
or the db's that have a guest account.
sp_MSdbuseraccess returns all databases (when run from master).
query analyzer therefore only shows the user the databases that user can
actually get into which is what i'd really like enterprise manager to
do. i wonder why the two different standards between the two tools?
ch wrote:[vbcol=seagreen]
> i think i figured out how to do it.
> a few changes to master.dbo.sp_MSdbuseraccess
> Uri Dimant wrote:

db permissions in EM

i've got a user that has permissions to read and write in two
databases. that user has enterprise manager installed and uses it (i
know he shouldn't really have it, but that's another topic). he can see
all of the databases on the server. is there any way to make EM only
show him the two databases which he can access?ch
Unfortunatly,NO
"ch" <ch@.dontemailme.com> wrote in message
news:41862283.D2817F00@.dontemailme.com...
> i've got a user that has permissions to read and write in two
> databases. that user has enterprise manager installed and uses it (i
> know he shouldn't really have it, but that's another topic). he can see
> all of the databases on the server. is there any way to make EM only
> show him the two databases which he can access?|||i think i figured out how to do it.
a few changes to master.dbo.sp_MSdbuseraccess
Uri Dimant wrote:[vbcol=seagreen]
> ch
> Unfortunatly,NO
> "ch" <ch@.dontemailme.com> wrote in message
> news:41862283.D2817F00@.dontemailme.com...|||Which means you no longer have a supported SQL server installation. Be caref
ul when modifying system
objects. Not supported.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ch" <ch@.dontemailme.com> wrote in message news:41862EC4.7EF0C91A@.dontemailme.com...eagreen">
> i think i figured out how to do it.
> a few changes to master.dbo.sp_MSdbuseraccess
>|||ch
You can hide a system databases from seeing them with EM , but user
databases you cannot.
"ch" <ch@.dontemailme.com> wrote in message
news:41862EC4.7EF0C91A@.dontemailme.com...[vbcol=seagreen]
> i think i figured out how to do it.
> a few changes to master.dbo.sp_MSdbuseraccess
>
> Uri Dimant wrote:
see[vbcol=seagreen]|||this is sort of goofy.
query analyzer uses master.dbo.sp_MShasdbaccess intead of
master.dbo.sp_MSdbuseraccess.
sp_MShasdbaccess only returns the databases that the user can get into
or the db's that have a guest account.
sp_MSdbuseraccess returns all databases (when run from master).
query analyzer therefore only shows the user the databases that user can
actually get into which is what i'd really like enterprise manager to
do. i wonder why the two different standards between the two tools?
ch wrote:[vbcol=seagreen]
> i think i figured out how to do it.
> a few changes to master.dbo.sp_MSdbuseraccess
> Uri Dimant wrote:

db permissions in EM

i've got a user that has permissions to read and write in two
databases. that user has enterprise manager installed and uses it (i
know he shouldn't really have it, but that's another topic). he can see
all of the databases on the server. is there any way to make EM only
show him the two databases which he can access?ch
Unfortunatly,NO
"ch" <ch@.dontemailme.com> wrote in message
news:41862283.D2817F00@.dontemailme.com...
> i've got a user that has permissions to read and write in two
> databases. that user has enterprise manager installed and uses it (i
> know he shouldn't really have it, but that's another topic). he can see
> all of the databases on the server. is there any way to make EM only
> show him the two databases which he can access?|||i think i figured out how to do it.
a few changes to master.dbo.sp_MSdbuseraccess
Uri Dimant wrote:
> ch
> Unfortunatly,NO
> "ch" <ch@.dontemailme.com> wrote in message
> news:41862283.D2817F00@.dontemailme.com...
> > i've got a user that has permissions to read and write in two
> > databases. that user has enterprise manager installed and uses it (i
> > know he shouldn't really have it, but that's another topic). he can see
> > all of the databases on the server. is there any way to make EM only
> > show him the two databases which he can access?|||Which means you no longer have a supported SQL server installation. Be careful when modifying system
objects. Not supported.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ch" <ch@.dontemailme.com> wrote in message news:41862EC4.7EF0C91A@.dontemailme.com...
> i think i figured out how to do it.
> a few changes to master.dbo.sp_MSdbuseraccess
>|||ch
You can hide a system databases from seeing them with EM , but user
databases you cannot.
"ch" <ch@.dontemailme.com> wrote in message
news:41862EC4.7EF0C91A@.dontemailme.com...
> i think i figured out how to do it.
> a few changes to master.dbo.sp_MSdbuseraccess
>
> Uri Dimant wrote:
> >
> > ch
> > Unfortunatly,NO
> >
> > "ch" <ch@.dontemailme.com> wrote in message
> > news:41862283.D2817F00@.dontemailme.com...
> > > i've got a user that has permissions to read and write in two
> > > databases. that user has enterprise manager installed and uses it (i
> > > know he shouldn't really have it, but that's another topic). he can
see
> > > all of the databases on the server. is there any way to make EM only
> > > show him the two databases which he can access?|||this is sort of goofy.
query analyzer uses master.dbo.sp_MShasdbaccess intead of
master.dbo.sp_MSdbuseraccess.
sp_MShasdbaccess only returns the databases that the user can get into
or the db's that have a guest account.
sp_MSdbuseraccess returns all databases (when run from master).
query analyzer therefore only shows the user the databases that user can
actually get into which is what i'd really like enterprise manager to
do. i wonder why the two different standards between the two tools?
ch wrote:
> i think i figured out how to do it.
> a few changes to master.dbo.sp_MSdbuseraccess
> Uri Dimant wrote:
> >
> > ch
> > Unfortunatly,NO
> >
> > "ch" <ch@.dontemailme.com> wrote in message
> > news:41862283.D2817F00@.dontemailme.com...
> > > i've got a user that has permissions to read and write in two
> > > databases. that user has enterprise manager installed and uses it (i
> > > know he shouldn't really have it, but that's another topic). he can see
> > > all of the databases on the server. is there any way to make EM only
> > > show him the two databases which he can access?

db permissions

Hi
I have a query in relation to db permissions. I created a local Windows
login and then created a SQL Server login using the same account. I gave this
login access just to one database and assigned him to the db_denydatawriter
role in that databse. This works fine as I just want them to have SELECT
access only on that db. The problem is that that login can still do SELECT,
UPDATE, DELETE and INSERT on other db's within the server.
I thought that if you didn't select Permit against a database in the
Database Access screen that the user would not have access to the db?
What is the best way to prevent this user from accessing any db's apart from
the one which I specified. I could obviously go through each db and specify
db_denydatawriter but on servers with lots of db's that is not really
practical.
Thanks in advance"jonjo" <jonjo@.discussions.microsoft.com> wrote in message
news:3DA9DC0E-FEA9-48DD-A7B6-CBC6FAAA0D92@.microsoft.com...
> Hi
> I have a query in relation to db permissions. I created a local Windows
> login and then created a SQL Server login using the same account. I gave
> this
> login access just to one database and assigned him to the
> db_denydatawriter
> role in that databse. This works fine as I just want them to have SELECT
> access only on that db. The problem is that that login can still do
> SELECT,
> UPDATE, DELETE and INSERT on other db's within the server.
> I thought that if you didn't select Permit against a database in the
> Database Access screen that the user would not have access to the db?
> What is the best way to prevent this user from accessing any db's apart
> from
> the one which I specified. I could obviously go through each db and
> specify
> db_denydatawriter but on servers with lots of db's that is not really
> practical.
> Thanks in advance
Check those other DB's for a guest account. If you don't have explicit
access to a database and it has a guest account, then your login will use
that guest account.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks Rick
I'll do that
"Rick Sawtell" wrote:
> "jonjo" <jonjo@.discussions.microsoft.com> wrote in message
> news:3DA9DC0E-FEA9-48DD-A7B6-CBC6FAAA0D92@.microsoft.com...
> > Hi
> >
> > I have a query in relation to db permissions. I created a local Windows
> > login and then created a SQL Server login using the same account. I gave
> > this
> > login access just to one database and assigned him to the
> > db_denydatawriter
> > role in that databse. This works fine as I just want them to have SELECT
> > access only on that db. The problem is that that login can still do
> > SELECT,
> > UPDATE, DELETE and INSERT on other db's within the server.
> >
> > I thought that if you didn't select Permit against a database in the
> > Database Access screen that the user would not have access to the db?
> >
> > What is the best way to prevent this user from accessing any db's apart
> > from
> > the one which I specified. I could obviously go through each db and
> > specify
> > db_denydatawriter but on servers with lots of db's that is not really
> > practical.
> >
> > Thanks in advance
> Check those other DB's for a guest account. If you don't have explicit
> access to a database and it has a guest account, then your login will use
> that guest account.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

db permissions

Hi
I have a query in relation to db permissions. I created a local Windows
login and then created a SQL Server login using the same account. I gave this
login access just to one database and assigned him to the db_denydatawriter
role in that databse. This works fine as I just want them to have SELECT
access only on that db. The problem is that that login can still do SELECT,
UPDATE, DELETE and INSERT on other db's within the server.
I thought that if you didn't select Permit against a database in the
Database Access screen that the user would not have access to the db?
What is the best way to prevent this user from accessing any db's apart from
the one which I specified. I could obviously go through each db and specify
db_denydatawriter but on servers with lots of db's that is not really
practical.
Thanks in advance
"jonjo" <jonjo@.discussions.microsoft.com> wrote in message
news:3DA9DC0E-FEA9-48DD-A7B6-CBC6FAAA0D92@.microsoft.com...
> Hi
> I have a query in relation to db permissions. I created a local Windows
> login and then created a SQL Server login using the same account. I gave
> this
> login access just to one database and assigned him to the
> db_denydatawriter
> role in that databse. This works fine as I just want them to have SELECT
> access only on that db. The problem is that that login can still do
> SELECT,
> UPDATE, DELETE and INSERT on other db's within the server.
> I thought that if you didn't select Permit against a database in the
> Database Access screen that the user would not have access to the db?
> What is the best way to prevent this user from accessing any db's apart
> from
> the one which I specified. I could obviously go through each db and
> specify
> db_denydatawriter but on servers with lots of db's that is not really
> practical.
> Thanks in advance
Check those other DB's for a guest account. If you don't have explicit
access to a database and it has a guest account, then your login will use
that guest account.
Rick Sawtell
MCT, MCSD, MCDBA
|||Thanks Rick
I'll do that
"Rick Sawtell" wrote:

> "jonjo" <jonjo@.discussions.microsoft.com> wrote in message
> news:3DA9DC0E-FEA9-48DD-A7B6-CBC6FAAA0D92@.microsoft.com...
> Check those other DB's for a guest account. If you don't have explicit
> access to a database and it has a guest account, then your login will use
> that guest account.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

db permissions

Hi
I have a query in relation to db permissions. I created a local Windows
login and then created a SQL Server login using the same account. I gave thi
s
login access just to one database and assigned him to the db_denydatawriter
role in that databse. This works fine as I just want them to have SELECT
access only on that db. The problem is that that login can still do SELECT,
UPDATE, DELETE and INSERT on other db's within the server.
I thought that if you didn't select Permit against a database in the
Database Access screen that the user would not have access to the db?
What is the best way to prevent this user from accessing any db's apart from
the one which I specified. I could obviously go through each db and specify
db_denydatawriter but on servers with lots of db's that is not really
practical.
Thanks in advance"jonjo" <jonjo@.discussions.microsoft.com> wrote in message
news:3DA9DC0E-FEA9-48DD-A7B6-CBC6FAAA0D92@.microsoft.com...
> Hi
> I have a query in relation to db permissions. I created a local Windows
> login and then created a SQL Server login using the same account. I gave
> this
> login access just to one database and assigned him to the
> db_denydatawriter
> role in that databse. This works fine as I just want them to have SELECT
> access only on that db. The problem is that that login can still do
> SELECT,
> UPDATE, DELETE and INSERT on other db's within the server.
> I thought that if you didn't select Permit against a database in the
> Database Access screen that the user would not have access to the db?
> What is the best way to prevent this user from accessing any db's apart
> from
> the one which I specified. I could obviously go through each db and
> specify
> db_denydatawriter but on servers with lots of db's that is not really
> practical.
> Thanks in advance
Check those other DB's for a guest account. If you don't have explicit
access to a database and it has a guest account, then your login will use
that guest account.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks Rick
I'll do that
"Rick Sawtell" wrote:

> "jonjo" <jonjo@.discussions.microsoft.com> wrote in message
> news:3DA9DC0E-FEA9-48DD-A7B6-CBC6FAAA0D92@.microsoft.com...
> Check those other DB's for a guest account. If you don't have explicit
> access to a database and it has a guest account, then your login will use
> that guest account.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>