Database Structure

Hi Everyone
I Have a Question Concerning DataBase Structure,
If i have a database that contains All Master Tables [user acount,user
detail &...] & i have to make another module for the same system that will
use the same master tables
Is it Preferred To Construct A New Database for this module & any any other
new module or make it all in the same database because they all shared the
same master Data?
Any Help Will Be Appreciated
Hi
Size tends to be one of the drivers as to whether you should partition, if
it is a reasonable size then keep them together. If you used views to access
the data then it would be quite easy to partition it at a later point.
John
"Mariame" <mariame_waguih@.hotmail.com> wrote in message
news:uPlhibieFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi Everyone
> I Have a Question Concerning DataBase Structure,
> If i have a database that contains All Master Tables [user acount,user
> detail &...] & i have to make another module for the same system that
> will use the same master tables
> Is it Preferred To Construct A New Database for this module & any any
> other new module or make it all in the same database because they all
> shared the same master Data?
> Any Help Will Be Appreciated
>
|||As John Suggests, Absolutely, positively use views so you can move things if
you wish..
I prefer ( if size permits) to have everything in a single database...
However you may wish to place different modules in different filegroups IF
you think you may wish to backup/restore a module independently of the
others..
If you put things in different databases, remember things can get out of
sync, unless you shut everything down for backups... Also there can be no
cross-database referential integrity...
Try to put them together in the db, but separate if you must.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mariame" <mariame_waguih@.hotmail.com> wrote in message
news:uPlhibieFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi Everyone
> I Have a Question Concerning DataBase Structure,
> If i have a database that contains All Master Tables [user acount,user
> detail &...] & i have to make another module for the same system that
> will use the same master tables
> Is it Preferred To Construct A New Database for this module & any any
> other new module or make it all in the same database because they all
> shared the same master Data?
> Any Help Will Be Appreciated
>
|||"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eXcEc7keFHA.2736@.TK2MSFTNGP12.phx.gbl...
> As John Suggests, Absolutely, positively use views so you can move things
> if you wish..
> I prefer ( if size permits) to have everything in a single database...
> However you may wish to place different modules in different filegroups IF
> you think you may wish to backup/restore a module independently of the
> others..
> If you put things in different databases, remember things can get out of
> sync, unless you shut everything down for backups... Also there can be no
> cross-database referential integrity...
> Try to put them together in the db, but separate if you must.
>
I agree. But I would go further and say that when you are designing a
system from the ground-up, you never "must". If you think you must seperate
related objects into different databases, think again. Schemas, FileGroups,
views, permissions, etc will usually let you keep the objects in one
database.
David
|||If you place your master data in several databases, then you may end up with
lots of duplicate for indexes, views, triggers, procedures etc, it probably
does not worth unless your table will be really big.
"Mariame" <mariame_waguih@.hotmail.com> wrote in message
news:uPlhibieFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi Everyone
> I Have a Question Concerning DataBase Structure,
> If i have a database that contains All Master Tables [user acount,user
> detail &...] & i have to make another module for the same system that
> will use the same master tables
> Is it Preferred To Construct A New Database for this module & any any
> other new module or make it all in the same database because they all
> shared the same master Data?
> Any Help Will Be Appreciated
>

Database Structure

Hi Everyone
I Have a Question Concerning DataBase Structure,
If i have a database that contains All Master Tables [user acount,user
detail &...] & i have to make another module for the same system that will
use the same master tables
Is it Preferred To Construct A New Database for this module & any any other
new module or make it all in the same database because they all shared the
same master Data'
Any Help Will Be AppreciatedHi
Size tends to be one of the drivers as to whether you should partition, if
it is a reasonable size then keep them together. If you used views to access
the data then it would be quite easy to partition it at a later point.
John
"Mariame" <mariame_waguih@.hotmail.com> wrote in message
news:uPlhibieFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi Everyone
> I Have a Question Concerning DataBase Structure,
> If i have a database that contains All Master Tables [user acount,user
> detail &...] & i have to make another module for the same system that
> will use the same master tables
> Is it Preferred To Construct A New Database for this module & any any
> other new module or make it all in the same database because they all
> shared the same master Data'
> Any Help Will Be Appreciated
>|||As John Suggests, Absolutely, positively use views so you can move things if
you wish..
I prefer ( if size permits) to have everything in a single database...
However you may wish to place different modules in different filegroups IF
you think you may wish to backup/restore a module independently of the
others..
If you put things in different databases, remember things can get out of
sync, unless you shut everything down for backups... Also there can be no
cross-database referential integrity...
Try to put them together in the db, but separate if you must.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mariame" <mariame_waguih@.hotmail.com> wrote in message
news:uPlhibieFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi Everyone
> I Have a Question Concerning DataBase Structure,
> If i have a database that contains All Master Tables [user acount,user
> detail &...] & i have to make another module for the same system that
> will use the same master tables
> Is it Preferred To Construct A New Database for this module & any any
> other new module or make it all in the same database because they all
> shared the same master Data'
> Any Help Will Be Appreciated
>|||"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eXcEc7keFHA.2736@.TK2MSFTNGP12.phx.gbl...
> As John Suggests, Absolutely, positively use views so you can move things
> if you wish..
> I prefer ( if size permits) to have everything in a single database...
> However you may wish to place different modules in different filegroups IF
> you think you may wish to backup/restore a module independently of the
> others..
> If you put things in different databases, remember things can get out of
> sync, unless you shut everything down for backups... Also there can be no
> cross-database referential integrity...
> Try to put them together in the db, but separate if you must.
>
I agree. But I would go further and say that when you are designing a
system from the ground-up, you never "must". If you think you must seperate
related objects into different databases, think again. Schemas, FileGroups,
views, permissions, etc will usually let you keep the objects in one
database.
David|||If you place your master data in several databases, then you may end up with
lots of duplicate for indexes, views, triggers, procedures etc, it probably
does not worth unless your table will be really big.
"Mariame" <mariame_waguih@.hotmail.com> wrote in message
news:uPlhibieFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi Everyone
> I Have a Question Concerning DataBase Structure,
> If i have a database that contains All Master Tables [user acount,user
> detail &...] & i have to make another module for the same system that
> will use the same master tables
> Is it Preferred To Construct A New Database for this module & any any
> other new module or make it all in the same database because they all
> shared the same master Data'
> Any Help Will Be Appreciated
>

Database Size Blowout - I mean like HUGE!

Has anybody come across a problem where their database
file becomes enormous for no particular reason?
I have checked all user and system tables, and they are
correct. It seems that in 3 days the database has blown
out from 400MB to 18500MB.
Any ideas/suggestions would be greatly appreciated.
Thank youIs it the database or log? run:
dbcc sqlperf('logspace')
and see if it is your log file, if it is, back it up and shrink the file or
see if you have any open transactions "dbcc opentran"
HTH
Ray Higdon MCSE, MCDBA, CCNA
--
"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in message
news:d94c01c3f03b$e1ef7810$a101280a@.phx.gbl...
> Has anybody come across a problem where their database
> file becomes enormous for no particular reason?
> I have checked all user and system tables, and they are
> correct. It seems that in 3 days the database has blown
> out from 400MB to 18500MB.
> Any ideas/suggestions would be greatly appreciated.
> Thank you|||somebody might have pumped in huge data and db might be in full recovery
mode .. check log size, truncate log ,shrink db u shall gain yur db size
again.
i agree with ray
run dbcc sqlperf('logspace')
u'll know abt the log size and do the above mentioned steps.
backup file will be huge, if u have enough space back it up first to be on a
safer side.
Regards,
Mayur
"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in message
news:d94c01c3f03b$e1ef7810$a101280a@.phx.gbl...
> Has anybody come across a problem where their database
> file becomes enormous for no particular reason?
> I have checked all user and system tables, and they are
> correct. It seems that in 3 days the database has blown
> out from 400MB to 18500MB.
> Any ideas/suggestions would be greatly appreciated.
> Thank you|||I've already checked that. The log is currently using
50MB, whilst the PRIMARY database file is using 18329MB.
I've checked all the tables, and the row counts are what
they should be, back when the db was about 400MB.
Bizarre

>--Original Message--
>Is it the database or log? run:
>dbcc sqlperf('logspace')
>and see if it is your log file, if it is, back it up and
shrink the file or
>see if you have any open transactions "dbcc opentran"
>HTH
>--
>Ray Higdon MCSE, MCDBA, CCNA
>--
>"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in
message
>news:d94c01c3f03b$e1ef7810$a101280a@.phx.gbl...
blown
>
>.
>|||Indexes?
http://vyaskn.tripod.com/code/sp_show_huge_tables.txt
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in message
news:f0be01c3f0f4$c4533620$a501280a@.phx.gbl...
> I've already checked that. The log is currently using
> 50MB, whilst the PRIMARY database file is using 18329MB.
> I've checked all the tables, and the row counts are what
> they should be, back when the db was about 400MB.
> Bizarre
>
> shrink the file or
> message
> blown

Database Size Blowout - I mean like HUGE!

Has anybody come across a problem where their database
file becomes enormous for no particular reason?
I have checked all user and system tables, and they are
correct. It seems that in 3 days the database has blown
out from 400MB to 18500MB.
Any ideas/suggestions would be greatly appreciated.
Thank youIs it the database or log? run:
dbcc sqlperf('logspace')
and see if it is your log file, if it is, back it up and shrink the file or
see if you have any open transactions "dbcc opentran"
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in message
news:d94c01c3f03b$e1ef7810$a101280a@.phx.gbl...
> Has anybody come across a problem where their database
> file becomes enormous for no particular reason?
> I have checked all user and system tables, and they are
> correct. It seems that in 3 days the database has blown
> out from 400MB to 18500MB.
> Any ideas/suggestions would be greatly appreciated.
> Thank you|||somebody might have pumped in huge data and db might be in full recovery
mode .. check log size, truncate log ,shrink db u shall gain yur db size
again.
i agree with ray
run dbcc sqlperf('logspace')
u'll know abt the log size and do the above mentioned steps.
backup file will be huge, if u have enough space back it up first to be on a
safer side.
Regards,
Mayur
"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in message
news:d94c01c3f03b$e1ef7810$a101280a@.phx.gbl...
> Has anybody come across a problem where their database
> file becomes enormous for no particular reason?
> I have checked all user and system tables, and they are
> correct. It seems that in 3 days the database has blown
> out from 400MB to 18500MB.
> Any ideas/suggestions would be greatly appreciated.
> Thank you|||I've already checked that. The log is currently using
50MB, whilst the PRIMARY database file is using 18329MB.
I've checked all the tables, and the row counts are what
they should be, back when the db was about 400MB.
Bizarre
>--Original Message--
>Is it the database or log? run:
>dbcc sqlperf('logspace')
>and see if it is your log file, if it is, back it up and
shrink the file or
>see if you have any open transactions "dbcc opentran"
>HTH
>--
>Ray Higdon MCSE, MCDBA, CCNA
>--
>"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in
message
>news:d94c01c3f03b$e1ef7810$a101280a@.phx.gbl...
>> Has anybody come across a problem where their database
>> file becomes enormous for no particular reason?
>> I have checked all user and system tables, and they are
>> correct. It seems that in 3 days the database has
blown
>> out from 400MB to 18500MB.
>> Any ideas/suggestions would be greatly appreciated.
>> Thank you
>
>.
>|||Indexes?
http://vyaskn.tripod.com/code/sp_show_huge_tables.txt
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in message
news:f0be01c3f0f4$c4533620$a501280a@.phx.gbl...
> I've already checked that. The log is currently using
> 50MB, whilst the PRIMARY database file is using 18329MB.
> I've checked all the tables, and the row counts are what
> they should be, back when the db was about 400MB.
> Bizarre
>
> >--Original Message--
> >Is it the database or log? run:
> >
> >dbcc sqlperf('logspace')
> >
> >and see if it is your log file, if it is, back it up and
> shrink the file or
> >see if you have any open transactions "dbcc opentran"
> >
> >HTH
> >
> >--
> >Ray Higdon MCSE, MCDBA, CCNA
> >--
> >"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in
> message
> >news:d94c01c3f03b$e1ef7810$a101280a@.phx.gbl...
> >> Has anybody come across a problem where their database
> >> file becomes enormous for no particular reason?
> >>
> >> I have checked all user and system tables, and they are
> >> correct. It seems that in 3 days the database has
> blown
> >> out from 400MB to 18500MB.
> >>
> >> Any ideas/suggestions would be greatly appreciated.
> >>
> >> Thank you
> >
> >
> >.
> >

Database Security

Hi,
I have created a database in server SRV1 with user 'aaa' as database owner
Know if some body detach this database from SRV1 and attach them on other
server same SRV2 with defrent sa and defrent users 'SA' user in SRV2 has ful
l
access to may database
How can restric my database for other server and other sa youser ther ?
Tanks .
Daryoush AjamiHi,
First of all restirct the access to your SQL Server. In this case no one can
detach the database and attach in SRV2.
FYI, If he have detach database access in sql server and copy the files from
operating system then he will be able to
attach the database in his server and view all tables and objects.
Thanks
hari
SQL Server MVP
"Ajami" <Ajami@.discussions.microsoft.com> wrote in message
news:34C340B7-2451-4E9C-8227-EA0AA0DF7573@.microsoft.com...
> Hi,
> I have created a database in server SRV1 with user 'aaa' as database owner
> Know if some body detach this database from SRV1 and attach them on other
> server same SRV2 with defrent sa and defrent users 'SA' user in SRV2 has
> full
> access to may database
> How can restric my database for other server and other sa youser ther ?
> Tanks .
> --
> Daryoush Ajami

database security

i a the user of sql server 2005 on window server 2003. i want to know
that how can i prevent my database from other user logins because all
of them are sysadmin type.
and i am also looking for database concurrency control methods.

if any one know about this plz mail me answer on this mail id
mahendersingh_be@.yahoo.co.in
thanx in advanceOn 11 juin, 13:00, Mandy <mahendersing...@.gmail.comwrote:

Quote:

Originally Posted by

i a the user of sql server 2005 on window server 2003. i want to know
that how can i prevent my database from other user logins because all
of them are sysadmin type.
and i am also looking for database concurrency control methods.
>
if any one know about this plz mail me answer on this mail id
mahendersingh...@.yahoo.co.in
>
thanx in advance

Hi,
You can use SQL Server authentification, open MS SQL manager then
change the connection of the current server
Omar Abid|||Mandy (mahendersinghbe@.gmail.com) writes:

Quote:

Originally Posted by

i a the user of sql server 2005 on window server 2003. i want to know
that how can i prevent my database from other user logins because all
of them are sysadmin type.

In that case you would need to move the database to a different
instance where the other people can't get in. No protection from other
sysadmin on a aserver.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Database roles

I am not sure if this is the right place for this question or not but here it goes...

I want to add a new user to the database with read-only rights. I know public is selected by default and can not be changed. I also added the user to db_datareader and db_denydatawriter. But just as I was about to save I noticed Read Only. I have done some searching but have not found anything about this role. I assume it does just what it says but I am confused as to why it would be there when I can set the other settings I just did. Is there any benefit to using this role instead of the ones I chose? Should I add this role in addition to the two I have already picked? Any help is greatly appreciated.

thank you,

Kevin

If this is SQL Server 2000, then add them to db_datareader and db_denydatawriter. If this is SQL Server 2005, just grant select permissions on the database to the user.|||If the user is in those fixed database roles then need not worry about setting anything additional, as the user will be in READONLY for data.

Database Roles

If I set up a user with a database role of dbo, does that automatically give
him dbo rights on all objects in the database. For instance, can the user
then execute a stored procedure in the database, or do you have to explicitly
go in and put an "X" in the execute permission checkbox next to the stored
procedure?
Message posted via http://www.droptable.com
Robert,
By dbo I assume that you assigned the user to the db_owner role. If you
have, then that person has rights to do absolutely anything, within the
bounds of that database.
If you don't want him to be able to do absolutely anything, then place him
in another role and grant that role the needed rights.
RLF
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:571d2aa2f02d8@.uwe...
> If I set up a user with a database role of dbo, does that automatically
> give
> him dbo rights on all objects in the database. For instance, can the user
> then execute a stored procedure in the database, or do you have to
> explicitly
> go in and put an "X" in the execute permission checkbox next to the stored
> procedure?
> --
> Message posted via http://www.droptable.com
|||When granting the database owner role to the user, do you also need to put a
check mark (such as select, insert, update, delete) for access to a table, or
Exec for a stored procedure for the user to have those specified permissions
on the object, because when the user is granted the database owner role, all
the check boxes are blank.
Russell Fields wrote:[vbcol=seagreen]
>Robert,
>By dbo I assume that you assigned the user to the db_owner role. If you
>have, then that person has rights to do absolutely anything, within the
>bounds of that database.
>If you don't want him to be able to do absolutely anything, then place him
>in another role and grant that role the needed rights.
>RLF
>[quoted text clipped - 3 lines]
Message posted via http://www.droptable.com
|||Making a user member of 'db_owner' role will ensure that the user 'has all
permissions in the database'. No need of to put additinal check mark for
select etc.
"Robert R via droptable.com" wrote:

> When granting the database owner role to the user, do you also need to put a
> check mark (such as select, insert, update, delete) for access to a table, or
> Exec for a stored procedure for the user to have those specified permissions
> on the object, because when the user is granted the database owner role, all
> the check boxes are blank.

Database Roles

If I set up a user with a database role of dbo, does that automatically give
him dbo rights on all objects in the database. For instance, can the user
then execute a stored procedure in the database, or do you have to explicitl
y
go in and put an "X" in the execute permission checkbox next to the stored
procedure?
Message posted via http://www.droptable.comRobert,
By dbo I assume that you assigned the user to the db_owner role. If you
have, then that person has rights to do absolutely anything, within the
bounds of that database.
If you don't want him to be able to do absolutely anything, then place him
in another role and grant that role the needed rights.
RLF
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:571d2aa2f02d8@.uwe...
> If I set up a user with a database role of dbo, does that automatically
> give
> him dbo rights on all objects in the database. For instance, can the user
> then execute a stored procedure in the database, or do you have to
> explicitly
> go in and put an "X" in the execute permission checkbox next to the stored
> procedure?
> --
> Message posted via http://www.droptable.com|||When granting the database owner role to the user, do you also need to put a
check mark (such as select, insert, update, delete) for access to a table, o
r
Exec for a stored procedure for the user to have those specified permissions
on the object, because when the user is granted the database owner role, all
the check boxes are blank.
Russell Fields wrote:[vbcol=seagreen]
>Robert,
>By dbo I assume that you assigned the user to the db_owner role. If you
>have, then that person has rights to do absolutely anything, within the
>bounds of that database.
>If you don't want him to be able to do absolutely anything, then place him
>in another role and grant that role the needed rights.
>RLF
>
>[quoted text clipped - 3 lines]
Message posted via http://www.droptable.com|||Making a user member of 'db_owner' role will ensure that the user 'has all
permissions in the database'. No need of to put additinal check mark for
select etc.
"Robert R via droptable.com" wrote:

> When granting the database owner role to the user, do you also need to put
a
> check mark (such as select, insert, update, delete) for access to a table,
or
> Exec for a stored procedure for the user to have those specified permissio
ns
> on the object, because when the user is granted the database owner role, a
ll
> the check boxes are blank.

Database Roles

If I set up a user with a database role of dbo, does that automatically give
him dbo rights on all objects in the database. For instance, can the user
then execute a stored procedure in the database, or do you have to explicitly
go in and put an "X" in the execute permission checkbox next to the stored
procedure?
--
Message posted via http://www.sqlmonster.comRobert,
By dbo I assume that you assigned the user to the db_owner role. If you
have, then that person has rights to do absolutely anything, within the
bounds of that database.
If you don't want him to be able to do absolutely anything, then place him
in another role and grant that role the needed rights.
RLF
"Robert R via SQLMonster.com" <u3288@.uwe> wrote in message
news:571d2aa2f02d8@.uwe...
> If I set up a user with a database role of dbo, does that automatically
> give
> him dbo rights on all objects in the database. For instance, can the user
> then execute a stored procedure in the database, or do you have to
> explicitly
> go in and put an "X" in the execute permission checkbox next to the stored
> procedure?
> --
> Message posted via http://www.sqlmonster.com|||When granting the database owner role to the user, do you also need to put a
check mark (such as select, insert, update, delete) for access to a table, or
Exec for a stored procedure for the user to have those specified permissions
on the object, because when the user is granted the database owner role, all
the check boxes are blank.
Russell Fields wrote:
>Robert,
>By dbo I assume that you assigned the user to the db_owner role. If you
>have, then that person has rights to do absolutely anything, within the
>bounds of that database.
>If you don't want him to be able to do absolutely anything, then place him
>in another role and grant that role the needed rights.
>RLF
>> If I set up a user with a database role of dbo, does that automatically
>> give
>[quoted text clipped - 3 lines]
>> go in and put an "X" in the execute permission checkbox next to the stored
>> procedure?
--
Message posted via http://www.sqlmonster.com|||Making a user member of 'db_owner' role will ensure that the user 'has all
permissions in the database'. No need of to put additinal check mark for
select etc.
"Robert R via SQLMonster.com" wrote:
> When granting the database owner role to the user, do you also need to put a
> check mark (such as select, insert, update, delete) for access to a table, or
> Exec for a stored procedure for the user to have those specified permissions
> on the object, because when the user is granted the database owner role, all
> the check boxes are blank.

Database Role/User Query

Anyone have a tsql query that will give me a listing of database roles and their users already put together?try sp_helpuser with no arguments.

Database Role Security Permissions

Hi,

How can we determine permissions of a database role?

I could only find out how to determine user and login perms:

EXECUTE AS user = 'Omid' GO SELECT has_perms_by_name(db_name(), 'DATABASE', 'ANY') GO REVERT GOAny suggestions?I can't believe there is no reply after a day in MSDN forums. Anyway not to disappoint folks with the same problem, there is a very very stupid solution: Create a temp user(login) and add this user to the database role and then check the permissions and at last remove the user!|||

Hey, sometimes we all have to take a break

Are you using 2005? If so, you can use the sys.database_permissions view. Here is a blog that I forgot that I wrote about this until I started doing some research for you

http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1485.entry

This query gets table permissions in a database, with object names... Easy enough to expand for other types of objects:

select database_permissions.permission_name,
coalesce(objects.type_desc,database_permissions.class_desc)
+ case when objects.type_desc is not null and minor_id > 0 then '-COLUMN'
else '' end as object_type,
case database_permissions.class_desc
when 'SCHEMA' then schema_name(major_id)
when 'OBJECT_OR_COLUMN' then
case when minor_id = 0 then object_name(major_id)
else (select object_name(object_id) + '.'+ name
from sys.columns
where object_id = database_permissions.major_id
and column_id = database_permissions.minor_id) end
else 'other' end as object_name,
database_principals.name as database_principal,
database_permissions.state_desc as grant_state
from sys.database_permissions
join sys.database_principals
on database_permissions.grantee_principal_id = database_principals.principal_id
left join sys.objects --left because it is possible that it is a schema
on objects.object_id = database_permissions.major_id
where database_permissions.major_id > 0
and permission_name in ('SELECT','INSERT','UPDATE','DELETE')
order by object_name

|||

I really appreciate it. Although I've already implemented the "stupid" solution, I'll surely change it to use the query as soon as possible.

Thanks again.

Database Role - db_datareader

I am new to SQL Server.
When I add a new user (Like: Adam) to a database, I find
that Adam belongs to the "Public" database role. Can I
remove him from that Role ? If NOT, why ?
Besides, I would like to give Read access to the database,
someone suggests adding the db_datareader database role to
Adam. I would like to know does it mean that Adam can
read all Views / Stored Procedures / Tables ?
If we would like to upsize Access 2003 database to SQL
Server, does the View in SQL Server = Query in MS
Access ? Can view get parameters input ? What is the
difference between View and Stored Procedure ?
ThanksEveryone is a member of public. You cannot remove a user from public. If
you add Adam to the db_datareader role, then he can select from all tables
and views. It does not give him EXEC privileges on stored procs.
A query in Access can be migrated to a view or a stored proc in SQL Server.
Views cannot take parameters. However, stored procs and table-valued
user-defined functions in SQL Server can.
A view is essentially a re-usable SELECT. A proc is a program that can do
just about any DML statement.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:658601c52510$a868c4b0$a601280a@.phx.gbl...
I am new to SQL Server.
When I add a new user (Like: Adam) to a database, I find
that Adam belongs to the "Public" database role. Can I
remove him from that Role ? If NOT, why ?
Besides, I would like to give Read access to the database,
someone suggests adding the db_datareader database role to
Adam. I would like to know does it mean that Adam can
read all Views / Stored Procedures / Tables ?
If we would like to upsize Access 2003 database to SQL
Server, does the View in SQL Server = Query in MS
Access ? Can view get parameters input ? What is the
difference between View and Stored Procedure ?
Thanks|||You can't remove a user from the public role. It's a special
role and every user is a member of that role.
If a user is added to db_datareader database role, the user
can select from all user tables in the database.
A view is often like a query in Access but all Access
queries can not be made into views. So no...they aren't
necessarily equal.
There are no parameterized views in SQL Server. You can use
a table-valued user-defined function to simulate a
parameterized view.
A view is like a stored query (without parameters) or
sometimes referred to as a virtual table. Refer to books
online topic: SQL Views
for more information.
A stored procedure is one or more t-sql statements that can
be grouped together and executed as a single execution plan.
Refer to books online topic: SQL Stored Procedures
for more information.
Books online is an excellent resource. With being new to SQL
Server, you may want to take some time and read up on
concepts you aren't clear on, SQL Server architecture, etc.
-Sue
On Wed, 9 Mar 2005 17:29:52 -0800, "Paul"
<anonymous@.discussions.microsoft.com> wrote:
>I am new to SQL Server.
>When I add a new user (Like: Adam) to a database, I find
>that Adam belongs to the "Public" database role. Can I
>remove him from that Role ? If NOT, why ?
>Besides, I would like to give Read access to the database,
>someone suggests adding the db_datareader database role to
>Adam. I would like to know does it mean that Adam can
>read all Views / Stored Procedures / Tables ?
>If we would like to upsize Access 2003 database to SQL
>Server, does the View in SQL Server = Query in MS
>Access ? Can view get parameters input ? What is the
>difference between View and Stored Procedure ?
>Thanks|||Thank you for advice from both of you.
Does the VBA codes in MS Access will be migrated as User
Defined Function in SQL Server ?
Thanks|||If you use the upsizing wizard, it won't do anything with
modules or macros.
-Sue
On Wed, 9 Mar 2005 19:50:36 -0800, "Paul"
<anonymous@.discussions.microsoft.com> wrote:
>Thank you for advice from both of you.
>Does the VBA codes in MS Access will be migrated as User
>Defined Function in SQL Server ?
>Thanks|||Thank you for your advice.
However, if I have to migrate those codes, should I use
User Defined Functions ?
Thanks
>--Original Message--
>If you use the upsizing wizard, it won't do anything with
>modules or macros.
>-Sue
>On Wed, 9 Mar 2005 19:50:36 -0800, "Paul"
><anonymous@.discussions.microsoft.com> wrote:
>>Thank you for advice from both of you.
>>Does the VBA codes in MS Access will be migrated as User
>>Defined Function in SQL Server ?
>>Thanks
>.
>|||Not necessarily and most likely you won't find that you
could use much, if any, of your VBA code as user defined
functions. They aren't really equivalent.
-Sue
On Wed, 9 Mar 2005 20:16:19 -0800, "Paul"
<anonymous@.discussions.microsoft.com> wrote:
>Thank you for your advice.
>However, if I have to migrate those codes, should I use
>User Defined Functions ?
>Thanks
>>--Original Message--
>>If you use the upsizing wizard, it won't do anything with
>>modules or macros.
>>-Sue
>>On Wed, 9 Mar 2005 19:50:36 -0800, "Paul"
>><anonymous@.discussions.microsoft.com> wrote:
>>Thank you for advice from both of you.
>>Does the VBA codes in MS Access will be migrated as User
>>Defined Function in SQL Server ?
>>Thanks
>>.

Database Role - db_datareader

I am new to SQL Server.
When I add a new user (Like: Adam) to a database, I find
that Adam belongs to the "Public" database role. Can I
remove him from that Role ? If NOT, why ?
Besides, I would like to give Read access to the database,
someone suggests adding the db_datareader database role to
Adam. I would like to know does it mean that Adam can
read all Views / Stored Procedures / Tables ?
If we would like to upsize Access 2003 database to SQL
Server, does the View in SQL Server = Query in MS
Access ? Can view get parameters input ? What is the
difference between View and Stored Procedure ?
Thanks
Everyone is a member of public. You cannot remove a user from public. If
you add Adam to the db_datareader role, then he can select from all tables
and views. It does not give him EXEC privileges on stored procs.
A query in Access can be migrated to a view or a stored proc in SQL Server.
Views cannot take parameters. However, stored procs and table-valued
user-defined functions in SQL Server can.
A view is essentially a re-usable SELECT. A proc is a program that can do
just about any DML statement.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:658601c52510$a868c4b0$a601280a@.phx.gbl...
I am new to SQL Server.
When I add a new user (Like: Adam) to a database, I find
that Adam belongs to the "Public" database role. Can I
remove him from that Role ? If NOT, why ?
Besides, I would like to give Read access to the database,
someone suggests adding the db_datareader database role to
Adam. I would like to know does it mean that Adam can
read all Views / Stored Procedures / Tables ?
If we would like to upsize Access 2003 database to SQL
Server, does the View in SQL Server = Query in MS
Access ? Can view get parameters input ? What is the
difference between View and Stored Procedure ?
Thanks
|||You can't remove a user from the public role. It's a special
role and every user is a member of that role.
If a user is added to db_datareader database role, the user
can select from all user tables in the database.
A view is often like a query in Access but all Access
queries can not be made into views. So no...they aren't
necessarily equal.
There are no parameterized views in SQL Server. You can use
a table-valued user-defined function to simulate a
parameterized view.
A view is like a stored query (without parameters) or
sometimes referred to as a virtual table. Refer to books
online topic: SQL Views
for more information.
A stored procedure is one or more t-sql statements that can
be grouped together and executed as a single execution plan.
Refer to books online topic: SQL Stored Procedures
for more information.
Books online is an excellent resource. With being new to SQL
Server, you may want to take some time and read up on
concepts you aren't clear on, SQL Server architecture, etc.
-Sue
On Wed, 9 Mar 2005 17:29:52 -0800, "Paul"
<anonymous@.discussions.microsoft.com> wrote:

>I am new to SQL Server.
>When I add a new user (Like: Adam) to a database, I find
>that Adam belongs to the "Public" database role. Can I
>remove him from that Role ? If NOT, why ?
>Besides, I would like to give Read access to the database,
>someone suggests adding the db_datareader database role to
>Adam. I would like to know does it mean that Adam can
>read all Views / Stored Procedures / Tables ?
>If we would like to upsize Access 2003 database to SQL
>Server, does the View in SQL Server = Query in MS
>Access ? Can view get parameters input ? What is the
>difference between View and Stored Procedure ?
>Thanks
|||Thank you for advice from both of you.
Does the VBA codes in MS Access will be migrated as User
Defined Function in SQL Server ?
Thanks
|||If you use the upsizing wizard, it won't do anything with
modules or macros.
-Sue
On Wed, 9 Mar 2005 19:50:36 -0800, "Paul"
<anonymous@.discussions.microsoft.com> wrote:

>Thank you for advice from both of you.
>Does the VBA codes in MS Access will be migrated as User
>Defined Function in SQL Server ?
>Thanks
|||Thank you for your advice.
However, if I have to migrate those codes, should I use
User Defined Functions ?
Thanks

>--Original Message--
>If you use the upsizing wizard, it won't do anything with
>modules or macros.
>-Sue
>On Wed, 9 Mar 2005 19:50:36 -0800, "Paul"
><anonymous@.discussions.microsoft.com> wrote:
>
>.
>
|||Not necessarily and most likely you won't find that you
could use much, if any, of your VBA code as user defined
functions. They aren't really equivalent.
-Sue
On Wed, 9 Mar 2005 20:16:19 -0800, "Paul"
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thank you for your advice.
>However, if I have to migrate those codes, should I use
>User Defined Functions ?
>Thanks

Database Role - db_datareader

I am new to SQL Server.
When I add a new user (Like: Adam) to a database, I find
that Adam belongs to the "Public" database role. Can I
remove him from that Role ? If NOT, why ?
Besides, I would like to give Read access to the database,
someone suggests adding the db_datareader database role to
Adam. I would like to know does it mean that Adam can
read all Views / Stored Procedures / Tables ?
If we would like to upsize Access 2003 database to SQL
Server, does the View in SQL Server = Query in MS
Access ? Can view get parameters input ? What is the
difference between View and Stored Procedure ?
ThanksEveryone is a member of public. You cannot remove a user from public. If
you add Adam to the db_datareader role, then he can select from all tables
and views. It does not give him EXEC privileges on stored procs.
A query in Access can be migrated to a view or a stored proc in SQL Server.
Views cannot take parameters. However, stored procs and table-valued
user-defined functions in SQL Server can.
A view is essentially a re-usable SELECT. A proc is a program that can do
just about any DML statement.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:658601c52510$a868c4b0$a601280a@.phx.gbl...
I am new to SQL Server.
When I add a new user (Like: Adam) to a database, I find
that Adam belongs to the "Public" database role. Can I
remove him from that Role ? If NOT, why ?
Besides, I would like to give Read access to the database,
someone suggests adding the db_datareader database role to
Adam. I would like to know does it mean that Adam can
read all Views / Stored Procedures / Tables ?
If we would like to upsize Access 2003 database to SQL
Server, does the View in SQL Server = Query in MS
Access ? Can view get parameters input ? What is the
difference between View and Stored Procedure ?
Thanks|||You can't remove a user from the public role. It's a special
role and every user is a member of that role.
If a user is added to db_datareader database role, the user
can select from all user tables in the database.
A view is often like a query in Access but all Access
queries can not be made into views. So no...they aren't
necessarily equal.
There are no parameterized views in SQL Server. You can use
a table-valued user-defined function to simulate a
parameterized view.
A view is like a stored query (without parameters) or
sometimes referred to as a virtual table. Refer to books
online topic: SQL Views
for more information.
A stored procedure is one or more t-sql statements that can
be grouped together and executed as a single execution plan.
Refer to books online topic: SQL Stored Procedures
for more information.
Books online is an excellent resource. With being new to SQL
Server, you may want to take some time and read up on
concepts you aren't clear on, SQL Server architecture, etc.
-Sue
On Wed, 9 Mar 2005 17:29:52 -0800, "Paul"
<anonymous@.discussions.microsoft.com> wrote:

>I am new to SQL Server.
>When I add a new user (Like: Adam) to a database, I find
>that Adam belongs to the "Public" database role. Can I
>remove him from that Role ? If NOT, why ?
>Besides, I would like to give Read access to the database,
>someone suggests adding the db_datareader database role to
>Adam. I would like to know does it mean that Adam can
>read all Views / Stored Procedures / Tables ?
>If we would like to upsize Access 2003 database to SQL
>Server, does the View in SQL Server = Query in MS
>Access ? Can view get parameters input ? What is the
>difference between View and Stored Procedure ?
>Thanks|||Thank you for advice from both of you.
Does the VBA codes in MS Access will be migrated as User
Defined Function in SQL Server ?
Thanks|||If you use the upsizing wizard, it won't do anything with
modules or macros.
-Sue
On Wed, 9 Mar 2005 19:50:36 -0800, "Paul"
<anonymous@.discussions.microsoft.com> wrote:

>Thank you for advice from both of you.
>Does the VBA codes in MS Access will be migrated as User
>Defined Function in SQL Server ?
>Thanks|||Thank you for your advice.
However, if I have to migrate those codes, should I use
User Defined Functions ?
Thanks

>--Original Message--
>If you use the upsizing wizard, it won't do anything with
>modules or macros.
>-Sue
>On Wed, 9 Mar 2005 19:50:36 -0800, "Paul"
><anonymous@.discussions.microsoft.com> wrote:
>
>.
>|||Not necessarily and most likely you won't find that you
could use much, if any, of your VBA code as user defined
functions. They aren't really equivalent.
-Sue
On Wed, 9 Mar 2005 20:16:19 -0800, "Paul"
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thank you for your advice.
>However, if I have to migrate those codes, should I use
>User Defined Functions ?
>Thanks
>

Database Restore SQL 2005

Hi,
How do I allow a user permission to restore a database? Users can backup
databases but can't restore them? User is dbo on the database.
Same permisssions seemed to work fine in 2000.
--
Bill Ross
Denver, COIt probably worked before because the db already existed on the server.
From BooksOnLine under RESTORE DATABASE:
If the database being restored does not exist, the user must have CREATE
DATABASE permissions to be able to execute RESTORE. If the database exists,
RESTORE permissions default to members of the sysadmin and dbcreator fixed
server roles and the owner (dbo) of the database (for the FROM
DATABASE_SNAPSHOT option, the database always exists).
Andrew J. Kelly SQL MVP
"Bill Ross" <BillRoss@.discussions.microsoft.com> wrote in message
news:CB83664C-DEEF-40B0-82EF-7A688206DCE2@.microsoft.com...
> Hi,
> How do I allow a user permission to restore a database? Users can backup
> databases but can't restore them? User is dbo on the database.
> Same permisssions seemed to work fine in 2000.
> --
> Bill Ross
> Denver, CO|||Hi Andrew,
Thanks for the info. The user is dbo on the database and the database
exists? Do users need to be part of the dbcreator role? Does that mean tha
t
a user cannot restore a damaged db unless they have rights to create any db?
Most confusing.
Bill Ross
Denver, CO
"Andrew J. Kelly" wrote:

> It probably worked before because the db already existed on the server.
> From BooksOnLine under RESTORE DATABASE:
> If the database being restored does not exist, the user must have CREATE
> DATABASE permissions to be able to execute RESTORE. If the database exists
,
> RESTORE permissions default to members of the sysadmin and dbcreator fixed
> server roles and the owner (dbo) of the database (for the FROM
> DATABASE_SNAPSHOT option, the database always exists).
>
> --
> Andrew J. Kelly SQL MVP
> "Bill Ross" <BillRoss@.discussions.microsoft.com> wrote in message
> news:CB83664C-DEEF-40B0-82EF-7A688206DCE2@.microsoft.com...
>
>|||Bill
Do users need to be part of the dbcreator role?
Yes, or you can grant CREATE DATABASE permissions to be able to execute
RESTORE as Andrew pointed.
Does that mean that
> a user cannot restore a damaged db unless they have rights to create any
> db?
If the database does not exist they cannot unless they got CREATE DATABASE
permissions or members of dbcreator role
If the database is corrupt the user must be a memeber of dbcreator role
,more over he/she should has access to msdb dataabse in order to write a
history of backups/restores
"Bill Ross" <BillRoss@.discussions.microsoft.com> wrote in message
news:866D621A-EE05-48B9-9252-A603AD7A0154@.microsoft.com...[vbcol=seagreen]
> Hi Andrew,
> Thanks for the info. The user is dbo on the database and the database
> exists? Do users need to be part of the dbcreator role? Does that mean
> that
> a user cannot restore a damaged db unless they have rights to create any
> db?
> Most confusing.
> --
> Bill Ross
> Denver, CO
>
> "Andrew J. Kelly" wrote:
>

Database restore failed w/ConnectionRead (WrapperRead()) error

User attempts to restore database and receives the following error:
Title Bar:
Microsoft SQL-DMO (ODBC SQLState: 01000)
Error:
ConnectionRead (WrapperRead())
General network error. Check your network documentation.
Processed 1 pages for database 'xxxx', file 'xxxx' on file 1.
Once the user clicks past the errors and refreshes their databases,
the database does show up in the list and it was successfully
restored.Hello
Could you please send the SQL errorlog from the time that this failure
occured.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

DataBase referencial intergrity check

I allow the user to delete record1 from SQL Table1 and record2 from Table2. The only problem is, record1 and record2 refers to record3 in Table3 and I can′t allow the user to delete record1 if the is a field in record3 with record1 ref. code. I can′t set FK between them cuz there is more than one reference to the same field.

Can someone point the best solution for my problem?

Hope I'm not getting the tables mixed up, but try this:

Table 3 is a "parent" of table1, meaning that every row in table1 must have a matching ref code in table3 (iow, you can't delete a row in table3 if there's a matching ref code in table1)

alter table Table1
add constraint FK_Table1RefCode
foreign key (RefCode)
references Table3(RefCode)

Same goes for table2:

alter table Table2
add constraint FK_Table2RefCode
foreign key (RefCode)
references Table3(RefCode)

I hope I understood your question!

|||

That almost right, codefield1 Table1 and codefield2 Table2 and must ref. the same codefield in Table3(Parent Table); Table1 and 2 both have only two fields. I already added the constraints but Visual C# shows an error message when Insertin'

|||

Mr.Rezende:

Visual C# shows an error message when Insertin'

What's the error?

Also, please post your DDL

Database Read-only

When a database is retired and have to be read-only - only allow user to read tables under retired database however how I can grant to new user login into retired database with read-only. It won't me grant new user login. Only exist login id.
Thanks,
Mark
This problem is caused because the database is READ-ONLY. Meaning you can
not insert new users into the SYSUSERS table because it is READ-ONLY. I
might consider putting the database in DBO-USE-ONLY mode and then removing
the READ-ONLY, insert the new user and the place the database back into
READ-ONLY, and then remove the DBO-USE-ONLY usage. This will keep the
database from being update for normal users, but will also lock them out
while you are updating the database.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Mark Call" <anonymous@.discussions.microsoft.com> wrote in message
news:9CCDB045-4AAD-453B-9BAC-24EFC40A976F@.microsoft.com...
> When a database is retired and have to be read-only - only allow user to
read tables under retired database however how I can grant to new user login
into retired database with read-only. It won't me grant new user login. Only
exist login id.
> Thanks,
> Mark
|||Thank you for suggestion.
Thanks,
Mark

Database Read-only

When a database is retired and have to be read-only - only allow user to rea
d tables under retired database however how I can grant to new user login in
to retired database with read-only. It won't me grant new user login. Only
exist login id.
Thanks,
MarkThis problem is caused because the database is READ-ONLY. Meaning you can
not insert new users into the SYSUSERS table because it is READ-ONLY. I
might consider putting the database in DBO-USE-ONLY mode and then removing
the READ-ONLY, insert the new user and the place the database back into
READ-ONLY, and then remove the DBO-USE-ONLY usage. This will keep the
database from being update for normal users, but will also lock them out
while you are updating the database.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Mark Call" <anonymous@.discussions.microsoft.com> wrote in message
news:9CCDB045-4AAD-453B-9BAC-24EFC40A976F@.microsoft.com...
> When a database is retired and have to be read-only - only allow user to
read tables under retired database however how I can grant to new user login
into retired database with read-only. It won't me grant new user login. Only
exist login id.
> Thanks,
> Mark|||Thank you for suggestion.
Thanks,
Mark