Database Snapshots question

Dear all,
How can I make a snapshot in sql25k? When I do click on the option 'Database
snapshots' only appears 'refresh'.
Thanks for any input,from bol
CREATE DATABASE AdventureWorks_dbss1800 ON
( NAME = AdventureWorks_Data, FILENAME =
'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\AdventureWorks_data_1800.ss' )
AS SNAPSHOT OF AdventureWorks;
GO
"Enric" wrote:

> Dear all,
> How can I make a snapshot in sql25k? When I do click on the option 'Databa
se
> snapshots' only appears 'refresh'.
> Thanks for any input,
>|||thanks a lot
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)
"Omnibuzz" wrote:
> from bol
> CREATE DATABASE AdventureWorks_dbss1800 ON
> ( NAME = AdventureWorks_Data, FILENAME =
> 'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\AdventureWorks_data_1800.ss' )
> AS SNAPSHOT OF AdventureWorks;
> GO
> --
>
>
> "Enric" wrote:
>sql

database size

Dear all
I have a SQL abc database. Recently, I right click database properity,the
space avaliable is zero, but i see the database file size and transcation lo
g
has some free space. Why the space avaliable is zero in that case and have
any impact about this database?
thanksVerify that your database has the 'Automatic grow file' property checked in
both your data and transaction log files.
If it is not enabled, you have some choices, like expanding the size of your
files manually, or enabling automatic grow file. Perhaps you want to go with
the default of 10 percent.
Ben Nevarez, MCDBA, OCP
"123" <123@.discussions.microsoft.com> wrote in message
news:518D80E7-98FF-4729-9BB4-A6DA6ACFAD90@.microsoft.com...
> Dear all
> I have a SQL abc database. Recently, I right click database properity,the
> space avaliable is zero, but i see the database file size and transcation
> log
> has some free space. Why the space avaliable is zero in that case and have
> any impact about this database?
> thanks
>|||Check the fragmentation of your tables. If you haven't already it may be
best to set up a database maintenance plan to reorganise your data and index
pages. The frequency you do this depends on the workload of your server.
"123" wrote:

> Dear all
> I have a SQL abc database. Recently, I right click database properity,the
> space avaliable is zero, but i see the database file size and transcation
log
> has some free space. Why the space avaliable is zero in that case and have
> any impact about this database?
> thanks
>

Database Size

Dear All,
I have been asked to disk space / show growth rates for
our database, including Tables, Indexes both Clustered and
Non clustered, plus anything else I can think of.
I can easily calculate the size of the table / page
however I cannot find out how to calculate the size of an
index. Can anyone help with either the formula or point me
to a resourse.
Thanks in Advance
JimHave a look at this:
http://www.sql-server-performance.c...p?TOPIC_ID=4924
/Magnus
"Jimbo" wrote:

> Dear All,
> I have been asked to disk space / show growth rates for
> our database, including Tables, Indexes both Clustered and
> Non clustered, plus anything else I can think of.
> I can easily calculate the size of the table / page
> however I cannot find out how to calculate the size of an
> index. Can anyone help with either the formula or point me
> to a resourse.
> Thanks in Advance
> Jim
>|||Thanks Magnus.
Jim

>--Original Message--
>Have a look at this:
>http://www.sql-server-performance.com/forum/topic.asp?
TOPIC_ID=4924
>/Magnus
>"Jimbo" wrote:
>
and[vbcol=seagreen]
an[vbcol=seagreen]
me[vbcol=seagreen]
>.
>sql

database size

Dear all
I have a SQL abc database. Recently, I right click database properity,the
space avaliable is zero, but i see the database file size and transcation log
has some free space. Why the space avaliable is zero in that case and have
any impact about this database?
thanksVerify that your database has the 'Automatic grow file' property checked in
both your data and transaction log files.
If it is not enabled, you have some choices, like expanding the size of your
files manually, or enabling automatic grow file. Perhaps you want to go with
the default of 10 percent.
Ben Nevarez, MCDBA, OCP
"123" <123@.discussions.microsoft.com> wrote in message
news:518D80E7-98FF-4729-9BB4-A6DA6ACFAD90@.microsoft.com...
> Dear all
> I have a SQL abc database. Recently, I right click database properity,the
> space avaliable is zero, but i see the database file size and transcation
> log
> has some free space. Why the space avaliable is zero in that case and have
> any impact about this database?
> thanks
>|||Check the fragmentation of your tables. If you haven't already it may be
best to set up a database maintenance plan to reorganise your data and index
pages. The frequency you do this depends on the workload of your server.
"123" wrote:
> Dear all
> I have a SQL abc database. Recently, I right click database properity,the
> space avaliable is zero, but i see the database file size and transcation log
> has some free space. Why the space avaliable is zero in that case and have
> any impact about this database?
> thanks
>

Database Size

Dear All,
I have been asked to disk space / show growth rates for
our database, including Tables, Indexes both Clustered and
Non clustered, plus anything else I can think of.
I can easily calculate the size of the table / page
however I cannot find out how to calculate the size of an
index. Can anyone help with either the formula or point me
to a resourse.
Thanks in Advance
JimHave a look at this:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=4924
/Magnus
"Jimbo" wrote:
> Dear All,
> I have been asked to disk space / show growth rates for
> our database, including Tables, Indexes both Clustered and
> Non clustered, plus anything else I can think of.
> I can easily calculate the size of the table / page
> however I cannot find out how to calculate the size of an
> index. Can anyone help with either the formula or point me
> to a resourse.
> Thanks in Advance
> Jim
>|||Thanks Magnus.
Jim
>--Original Message--
>Have a look at this:
>http://www.sql-server-performance.com/forum/topic.asp?
TOPIC_ID=4924
>/Magnus
>"Jimbo" wrote:
>> Dear All,
>> I have been asked to disk space / show growth rates for
>> our database, including Tables, Indexes both Clustered
and
>> Non clustered, plus anything else I can think of.
>> I can easily calculate the size of the table / page
>> however I cannot find out how to calculate the size of
an
>> index. Can anyone help with either the formula or point
me
>> to a resourse.
>> Thanks in Advance
>> Jim
>.
>

Database Size

Dear All,
I have been asked to disk space / show growth rates for
our database, including Tables, Indexes both Clustered and
Non clustered, plus anything else I can think of.
I can easily calculate the size of the table / page
however I cannot find out how to calculate the size of an
index. Can anyone help with either the formula or point me
to a resourse.
Thanks in Advance
Jim
Have a look at this:
http://www.sql-server-performance.co...?TOPIC_ID=4924
/Magnus
"Jimbo" wrote:

> Dear All,
> I have been asked to disk space / show growth rates for
> our database, including Tables, Indexes both Clustered and
> Non clustered, plus anything else I can think of.
> I can easily calculate the size of the table / page
> however I cannot find out how to calculate the size of an
> index. Can anyone help with either the formula or point me
> to a resourse.
> Thanks in Advance
> Jim
>
|||Thanks Magnus.
Jim

>--Original Message--
>Have a look at this:
>http://www.sql-server-performance.com/forum/topic.asp?
TOPIC_ID=4924[vbcol=seagreen]
>/Magnus
>"Jimbo" wrote:
and[vbcol=seagreen]
an[vbcol=seagreen]
me
>.
>
sql

database size

Dear all
I have a SQL abc database. Recently, I right click database properity,the
space avaliable is zero, but i see the database file size and transcation log
has some free space. Why the space avaliable is zero in that case and have
any impact about this database?
thanks
Verify that your database has the 'Automatic grow file' property checked in
both your data and transaction log files.
If it is not enabled, you have some choices, like expanding the size of your
files manually, or enabling automatic grow file. Perhaps you want to go with
the default of 10 percent.
Ben Nevarez, MCDBA, OCP
"123" <123@.discussions.microsoft.com> wrote in message
news:518D80E7-98FF-4729-9BB4-A6DA6ACFAD90@.microsoft.com...
> Dear all
> I have a SQL abc database. Recently, I right click database properity,the
> space avaliable is zero, but i see the database file size and transcation
> log
> has some free space. Why the space avaliable is zero in that case and have
> any impact about this database?
> thanks
>
|||Check the fragmentation of your tables. If you haven't already it may be
best to set up a database maintenance plan to reorganise your data and index
pages. The frequency you do this depends on the workload of your server.
"123" wrote:

> Dear all
> I have a SQL abc database. Recently, I right click database properity,the
> space avaliable is zero, but i see the database file size and transcation log
> has some free space. Why the space avaliable is zero in that case and have
> any impact about this database?
> thanks
>

Database Protection

Dear All,
I run a win2k domain. We will be bringing a bespoke SQL database system on
board in a few weeks. I want to ensure the integratity of this database by
putting some security measures in place. I have concerns that individuals
may try to take the database to a competitor by copying it on to CD or
sending it through email. I would like to put something in place that will
make the database useless if it goes outside my domain. Has anyone got any
ideas? Encryption?
Many Thanks,"John Barwell" <johnbarwell@.msmdirect.co.uk> wrote in message
news:dLYzc.15803$NK4.2608717@.stones.force9.net...
> I run a win2k domain. We will be bringing a bespoke SQL database system on
> board in a few weeks. I want to ensure the integratity of this database by
> putting some security measures in place. I have concerns that individuals
> may try to take the database to a competitor by copying it on to CD or
> sending it through email. I would like to put something in place that will
> make the database useless if it goes outside my domain. Has anyone got any
> ideas? Encryption?
>
The best way to prevent theft of your database is to ensure proper security
is in place; both at the physical server layer, SQL Server and database
access layer. Further, implement security protection to the backup's that
may be created. I know of nothing that would render the db useless outside
of a domain...
Steve|||John,
Encryption is possible (at least according to the adds) but I have not done
it. See:
http://www.netlib.com/
http://www.appsecinc.com/products/dbencrypt/mssql/
FWIW,
Russell Fields
"John Barwell" <johnbarwell@.msmdirect.co.uk> wrote in message
news:dLYzc.15803$NK4.2608717@.stones.force9.net...
> Dear All,
> I run a win2k domain. We will be bringing a bespoke SQL database system on
> board in a few weeks. I want to ensure the integratity of this database by
> putting some security measures in place. I have concerns that individuals
> may try to take the database to a competitor by copying it on to CD or
> sending it through email. I would like to put something in place that will
> make the database useless if it goes outside my domain. Has anyone got any
> ideas? Encryption?
> Many Thanks,
>|||Activecrypt Software provides encryption solution for MSSQL Server XP_CRYPT
(www.xpcrypt.com)
and protection for T-SQL code: stored procedures, user defined functions,
triggers. The code encrypted with SQL Shield (www.sql-shield.com) cannot be
decrypted with existing decryptors like dOMNAR's SQL Server SysComments
Decryptor.|||On 6/16/04 7:25 AM, in article dLYzc.15803$NK4.2608717@.stones.force9.net,
"John Barwell" <johnbarwell@.msmdirect.co.uk> wrote:

> I have concerns that individuals
> may try to take the database to a competitor by copying it on to CD or
> sending it through email.
It is important to first establish a policy that defines what data you wish
to protect from whom. In military terminology this would be the security
classifications of objects and the security clearance of the subjects. The
first step is to establish access control lists (ACLs) that obey the
security principle of least privileges when realizing your security policy.
Users should be granted privileges only to data which they need to complete
their duty. After this, you can audit access to individual records and
insure that they are accessed only on a need-to-know basis by those who do
have permissions to access them.
Once you have done the fundamentals, you can add an additional layer of
security by encrypting data both in transit (by using SSL connections) and
at rest. There are several third-party software and appliance products that
implement column-level encryption and key-management. These features are not
implemented in SQL Server 2000, but are on the way in SQL Server 2005.
I hope that helps. Please let me know if you have any other questions.
-Mark Shlimovich|||>These features are not
> implemented in SQL Server 2000, but are on the way in SQL Server 2005.
This seems to be good news. We have rejected MSSQL precisely because of its
weak security model. We spent one whole year researching and testing it.
Are you saying that the key management will allow one to lock out DBAs and
restrict access of specific tables or objects to specific keys that are
constructed outside of the DBA role or so-called sysadmin role? In other
words, if the db is detached and attached on another MS box could the
sysadmin or some phantom god-like role gain access to that db? This is the
mutli-million dollar question. Do I understand the impending functionality
correct here or I am I way off the mark?
"Mark Shlimovich" <t-marks@.microsoft.com> wrote in message
news:BCF9688E.1EBB%t-marks@.microsoft.com...
> On 6/16/04 7:25 AM, in article dLYzc.15803$NK4.2608717@.stones.force9.net,
> "John Barwell" <johnbarwell@.msmdirect.co.uk> wrote:
>
> It is important to first establish a policy that defines what data you
wish
> to protect from whom. In military terminology this would be the security
> classifications of objects and the security clearance of the subjects. The
> first step is to establish access control lists (ACLs) that obey the
> security principle of least privileges when realizing your security
policy.
> Users should be granted privileges only to data which they need to
complete
> their duty. After this, you can audit access to individual records and
> insure that they are accessed only on a need-to-know basis by those who do
> have permissions to access them.
> Once you have done the fundamentals, you can add an additional layer of
> security by encrypting data both in transit (by using SSL connections) and
> at rest. There are several third-party software and appliance products
that
> implement column-level encryption and key-management. These features are
not
> implemented in SQL Server 2000, but are on the way in SQL Server 2005.
> I hope that helps. Please let me know if you have any other questions.
> -Mark Shlimovich
>|||>if the db is detached and attached on another MS box could the
>sysadmin or some phantom god-like role gain access to that db?
Only if in addition to the database itself they had access to the keys. SQL
Server 2005 will provide a key management system.

Database Ownership

Dear Support,
Upon knowing the cross database chaining option in SP3 on SQL2000 Server, I
finally understood why I had troubles on our applications last year. I took
a 'giant' step to work around the issue last time and it is about time I sh
ould make it right now. I
am hoping if you could share some thought and have your comments on the foll
owing live scenario.
1. I have 3 databases(say A,B and C) and they are required together to serv
e 3 applications or 3 login users through Access's ADPs(say a,b and c). Each
frontend application is designed and programmed to update only on its own d
atabase, but they are allow
ed to pull data from the other two databases. For example, 'a' could read/w
rite on A database, but readonly on B and C database.
My SQL Server is in Windows Authentication mode. Say, if I make changes to
three users(again a,b,c) of their database access setting on database A,B an
d C by declaring all of them(a,b,c) to be the owner (dbo) of all three datab
ases, my first question is,
can I declare database ownership on more than one users, or say, can one dat
abase or its objects be owned by more than one login?
Second, from a performance standpoint, there will be no 'broken link' if I a
m correct, and may I assume the response time will be better to users?
Third, if all a,b,c users are all database owners, or say 'a' owns A,B,C dat
abase, and so as 'b' also owns A,B,C database, am I correct that I will lose
the capability to fine tuning the permission setting on database objects (s
uch as stored procedure exe
c., r/w on tables/fields) at database level on each database?
I know I should stop here but the cross database chaining concept is getting
very interesting to me as a DBA/Programmer and the scenario I brought up he
r is all I am facing in my shop. I hope you could pardon me by allowing me
to continue bring up the fo
llowing of my concerns:
If, say, I decide to integrate all three (a,b,c) applications into one, say
BigBoy, this new BigBoy will have read and write functions/buttons on all A,
B and C database. Now, the original users of a,b,c are now using only one a
pplication, the BigBoy. If
I want to fine tuning the read and write permissions on databases without re
lying on the fronend applications, am I correct to remove the dbowner role f
rom each of the login of the a,b,c user, and use/click the select, update,ex
ec, etc. on the object lis
t from the permission screen for each user?
2. May I assume that the three users(a,b,c) I refer to above, can be replac
ed by or applied to Window's user defined group?
3. My orginal intention is to use Role instead of group for setting the new
permission scheme, but I was told the Role can not span across databases.
Would you confirm on this, or if there is a workaround on Role? The reason
I try to use Role because m
y shop has Network administration personnel and I could separate security ta
sks between Network Admin and DB Admin by using user defined DB Role.
Thank you for looking into this matter.
Martin> my first question is, can I declare database ownership on more than one
users, or say, can one database or its objects be owned by more than one
login?
A database may be owned by only one login.

quote:

---

> Second, from a performance standpoint, there will be no 'broken link' if I

---

am correct, and may I assume the response time will be better to users?
An unbroken ownership chain eliminates extra security checking but I don't
believe the performance difference is noticeable for most applications. A
significant benefit of an unbroken ownership chain is that permissions on
referenced objects are not needed. This allows you can restrict access to
data through views and procedures. In a multi-database environment like
yours, you could create views referencing tables in the other databases and
then grant select permissions on the views.

quote:

---

> Third, if all a,b,c users are all database owners, or say 'a' owns A,B,C

---

database, and so as 'b' also owns A,B,C database, am I correct that I will
lose the capability to fine tuning the permission setting on database
objects (such as stored procedure exec., r/w on tables/fields) at database
level on each database?
You cannot deny permissions from the database owner. The database owner has
full permissions on all objects within the database.

quote:

---

> I know I should stop here but the cross database chaining concept is

---

getting very interesting to me as a DBA/Programmer and the scenario I
brought up her is all I am facing in my shop. I hope you could pardon me by
allowing me to continue bring up the following of my concerns:

quote:

---

> If, say, I decide to integrate all three (a,b,c) applications into one,

---

say BigBoy, this new BigBoy will have read and write functions/buttons on
all A,B and C database. Now, the original users of a,b,c are now using only
one application, the BigBoy. If I want to fine tuning the read and write
permissions on databases without relying on the fronend applications, am I
correct to remove the dbowner role from each of the login of the a,b,c user,
and use/click the select, update,exec, etc. on the object list from the
permission screen for each user?
The dbo user and the db_owner role have powerful permissions that are not
normally needed for application access. A best practice is to grant needed
object permissions to roles so that you can control security through user
role membership. This provides more control over permissions.

quote:

---

> 2. May I assume that the three users(a,b,c) I refer to above, can be

---

replaced by or applied to Window's user defined group?
Yes.

quote:

---

> 3. My orginal intention is to use Role instead of group for setting the

---

new permission scheme, but I was told the Role can not span across
databases. Would you confirm on this, or if there is a workaround on Role?
The reason I try to use Role because my shop has Network administration
personnel and I could separate security tasks between Network Admin and DB
Admin by using user defined DB Role.
Database roles and database users are specific to a particular database but
this really isn't a big deal since you can setup role permissions once and
then control access through role membership. Cross-database chaining
enables you to implement referencing views so that you don't need to create
roles in the other databases. However the logins still need access to the
other databases, either directly or via the guest user security context.
The scripts below illustrates how you can set this up.
-- setup role security with cross-database chaining
USE A
EXEC sp_changedbowner 'MyLogin'
EXEC sp_dboption 'A', 'db chaining', true
EXEC sp_addRole 'ApplicationA'
GRANT ALL ON MyTable TO ApplicationA
GRANT ALL ON MyProc TO ApplicationA
GRANT SELECT ON MyDatabaseB_MyTable_View TO ApplicationA
GRANT SELECT ON MyDatabaseC_MyTable_View TO ApplicationA
GO
USE B
EXEC sp_changedbowner 'MyLogin'
EXEC sp_dboption 'B', 'db chaining', true
EXEC sp_addRole 'ApplicationB'
GRANT ALL ON MyTable TO ApplicationB
GRANT ALL ON MyProc TO ApplicationB
GRANT SELECT ON MyDatabaseA_MyTable_View TO ApplicationB
GRANT SELECT ON MyDatabaseC_MyTable_View TO ApplicationB
GO
USE C
EXEC sp_changedbowner 'MyLogin'
EXEC sp_dboption 'C', 'db chaining', true
EXEC sp_addRole 'ApplicationC'
GRANT ALL ON MyTable TO ApplicationC
GRANT ALL ON MyProc TO ApplicationC
GRANT SELECT ON MyDatabaseA_MyTable_View TO ApplicationC
GRANT SELECT ON MyDatabaseB_MyTable_View TO ApplicationC
GO
-- user setup with cross-database chaining without guest user
EXEC A..sp_grantdbaccess 'MyDomain\UserA'
EXEC A..sp_grantdbaccess 'MyDomain\UserB'
EXEC A..sp_grantdbaccess 'MyDomain\UserC'
EXEC A..sp_addrolemember 'ApplicationA', 'MyDomain\UserA'
EXEC B..sp_grantdbaccess 'MyDomain\UserA'
EXEC B..sp_grantdbaccess 'MyDomain\UserB'
EXEC B..sp_grantdbaccess 'MyDomain\UserC'
EXEC B..sp_addrolemember 'ApplicationB', 'MyDomain\UserB'
EXEC C..sp_grantdbaccess 'MyDomain\UserA'
EXEC C..sp_grantdbaccess 'MyDomain\UserB'
EXEC C..sp_grantdbaccess 'MyDomain\UserC'
EXEC C..sp_addrolemember 'ApplicationC', 'MyDomain\UserC'
GO
-- user setup with cross-database chaining and guest user in each database
GO
EXEC A..sp_grantdbaccess 'MyDomain\UserA'
EXEC A..sp_addrolemember 'ApplicationA', 'MyDomain\UserA'
EXEC B..sp_grantdbaccess 'MyDomain\UserB'
EXEC B..sp_addrolemember 'ApplicationB', 'MyDomain\UserB'
EXEC C..sp_grantdbaccess 'MyDomain\UserC'
EXEC C..sp_addrolemember 'ApplicationC', 'MyDomain\UserC'
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Martin" <anonymous@.discussions.microsoft.com> wrote in message
news:2E80551C-9C11-43E6-9457-98EA275B4A2E@.microsoft.com...

quote:

---

> Dear Support,
> Upon knowing the cross database chaining option in SP3 on SQL2000 Server,

---

I finally understood why I had troubles on our applications last year. I
took a 'giant' step to work around the issue last time and it is about time
I should make it right now. I am hoping if you could share some thought and
have your comments on the following live scenario.

quote:

---

> 1. I have 3 databases(say A,B and C) and they are required together to

---

serve 3 applications or 3 login users through Access's ADPs(say a,b and c).
Each frontend application is designed and programmed to update only on its
own database, but they are allowed to pull data from the other two
databases. For example, 'a' could read/write on A database, but readonly on
B and C database.

quote:

---

> My SQL Server is in Windows Authentication mode. Say, if I make changes

---

to three users(again a,b,c) of their database access setting on database A,B
and C by declaring all of them(a,b,c) to be the owner (dbo) of all three
databases, my first question is, can I declare database ownership on more
than one users, or say, can one database or its objects be owned by more
than one login?

quote:

---

> Second, from a performance standpoint, there will be no 'broken link' if I

---

am correct, and may I assume the response time will be better to users?

quote:

---

> Third, if all a,b,c users are all database owners, or say 'a' owns A,B,C

---

database, and so as 'b' also owns A,B,C database, am I correct that I will
lose the capability to fine tuning the permission setting on database
objects (such as stored procedure exec., r/w on tables/fields) at database
level on each database?

quote:

---

> I know I should stop here but the cross database chaining concept is

---

getting very interesting to me as a DBA/Programmer and the scenario I
brought up her is all I am facing in my shop. I hope you could pardon me by
allowing me to continue bring up the following of my concerns:

quote:

---

> If, say, I decide to integrate all three (a,b,c) applications into one,

---

say BigBoy, this new BigBoy will have read and write functions/buttons on
all A,B and C database. Now, the original users of a,b,c are now using only
one application, the BigBoy. If I want to fine tuning the read and write
permissions on databases without relying on the fronend applications, am I
correct to remove the dbowner role from each of the login of the a,b,c user,
and use/click the select, update,exec, etc. on the object list from the
permission screen for each user?

quote:

---

> 2. May I assume that the three users(a,b,c) I refer to above, can be

---

replaced by or applied to Window's user defined group?

quote:

---

> 3. My orginal intention is to use Role instead of group for setting the

---

new permission scheme, but I was told the Role can not span across
databases. Would you confirm on this, or if there is a workaround on Role?
The reason I try to use Role because my shop has Network administration
personnel and I could separate security tasks between Network Admin and DB
Admin by using user defined DB Role.

quote:

---

> Thank you for looking into this matter.
> Martin
>

---

Database Ownership

Dear Support
Upon knowing the cross database chaining option in SP3 on SQL2000 Server, I finally understood why I had troubles on our applications last year. I took a 'giant' step to work around the issue last time and it is about time I should make it right now. I am hoping if you could share some thought and have your comments on the following live scenario
1. I have 3 databases(say A,B and C) and they are required together to serve 3 applications or 3 login users through Access's ADPs(say a,b and c). Each frontend application is designed and programmed to update only on its own database, but they are allowed to pull data from the other two databases. For example, 'a' could read/write on A database, but readonly on B and C database
My SQL Server is in Windows Authentication mode. Say, if I make changes to three users(again a,b,c) of their database access setting on database A,B and C by declaring all of them(a,b,c) to be the owner (dbo) of all three databases, my first question is, can I declare database ownership on more than one users, or say, can one database or its objects be owned by more than one login?
Second, from a performance standpoint, there will be no 'broken link' if I am correct, and may I assume the response time will be better to users?
Third, if all a,b,c users are all database owners, or say 'a' owns A,B,C database, and so as 'b' also owns A,B,C database, am I correct that I will lose the capability to fine tuning the permission setting on database objects (such as stored procedure exec., r/w on tables/fields) at database level on each database
I know I should stop here but the cross database chaining concept is getting very interesting to me as a DBA/Programmer and the scenario I brought up her is all I am facing in my shop. I hope you could pardon me by allowing me to continue bring up the following of my concerns
If, say, I decide to integrate all three (a,b,c) applications into one, say BigBoy, this new BigBoy will have read and write functions/buttons on all A,B and C database. Now, the original users of a,b,c are now using only one application, the BigBoy. If I want to fine tuning the read and write permissions on databases without relying on the fronend applications, am I correct to remove the dbowner role from each of the login of the a,b,c user, and use/click the select, update,exec, etc. on the object list from the permission screen for each user
2. May I assume that the three users(a,b,c) I refer to above, can be replaced by or applied to Window's user defined group?
3. My orginal intention is to use Role instead of group for setting the new permission scheme, but I was told the Role can not span across databases. Would you confirm on this, or if there is a workaround on Role? The reason I try to use Role because my shop has Network administration personnel and I could separate security tasks between Network Admin and DB Admin by using user defined DB Role
Thank you for looking into this matter
MartinAnswered in security. Please don't post the same question independently to
multiple groups.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Martin" <anonymous@.discussions.microsoft.com> wrote in message
news:6B0EF357-1938-437E-886D-1614BF004B5E@.microsoft.com...
> Dear Support,
> Upon knowing the cross database chaining option in SP3 on SQL2000 Server,
I finally understood why I had troubles on our applications last year. I
took a 'giant' step to work around the issue last time and it is about time
I should make it right now. I am hoping if you could share some thought and
have your comments on the following live scenario.
> 1. I have 3 databases(say A,B and C) and they are required together to
serve 3 applications or 3 login users through Access's ADPs(say a,b and c).
Each frontend application is designed and programmed to update only on its
own database, but they are allowed to pull data from the other two
databases. For example, 'a' could read/write on A database, but readonly on
B and C database.
> My SQL Server is in Windows Authentication mode. Say, if I make changes
to three users(again a,b,c) of their database access setting on database A,B
and C by declaring all of them(a,b,c) to be the owner (dbo) of all three
databases, my first question is, can I declare database ownership on more
than one users, or say, can one database or its objects be owned by more
than one login?
> Second, from a performance standpoint, there will be no 'broken link' if I
am correct, and may I assume the response time will be better to users?
> Third, if all a,b,c users are all database owners, or say 'a' owns A,B,C
database, and so as 'b' also owns A,B,C database, am I correct that I will
lose the capability to fine tuning the permission setting on database
objects (such as stored procedure exec., r/w on tables/fields) at database
level on each database?
> I know I should stop here but the cross database chaining concept is
getting very interesting to me as a DBA/Programmer and the scenario I
brought up her is all I am facing in my shop. I hope you could pardon me by
allowing me to continue bring up the following of my concerns:
> If, say, I decide to integrate all three (a,b,c) applications into one,
say BigBoy, this new BigBoy will have read and write functions/buttons on
all A,B and C database. Now, the original users of a,b,c are now using only
one application, the BigBoy. If I want to fine tuning the read and write
permissions on databases without relying on the fronend applications, am I
correct to remove the dbowner role from each of the login of the a,b,c user,
and use/click the select, update,exec, etc. on the object list from the
permission screen for each user?
> 2. May I assume that the three users(a,b,c) I refer to above, can be
replaced by or applied to Window's user defined group?
> 3. My orginal intention is to use Role instead of group for setting the
new permission scheme, but I was told the Role can not span across
databases. Would you confirm on this, or if there is a workaround on Role?
The reason I try to use Role because my shop has Network administration
personnel and I could separate security tasks between Network Admin and DB
Admin by using user defined DB Role.
> Thank you for looking into this matter.
> Martin

Database Owner

Dear friends
I have a question,please help me.
I have a database and one of my database user is the owner of my database.
If i detach my database , i can attach it on other instance of SQL SERVER
with user of 'sa'.I want to stop this action in term of security.
Thanks
Mehdi
Hi,
SA is the system administrator and you cant restrict the permissions to
attach the database.
Thanks
Hari
SQL Server MVP
"Mehdi" <Mehdi@.discussions.microsoft.com> wrote in message
news:E312383F-AB28-4B51-BE85-59B605BE118F@.microsoft.com...
> Dear friends
> I have a question,please help me.
> I have a database and one of my database user is the owner of my database.
> If i detach my database , i can attach it on other instance of SQL SERVER
> with user of 'sa'.I want to stop this action in term of security.
> Thanks
> Mehdi
>
|||However, AFTER you've re-attached the database you can change the
database owner using sp_changedbowner.
ALI
Hari Prasad wrote:[vbcol=seagreen]
> Hi,
> SA is the system administrator and you cant restrict the permissions to
> attach the database.
> Thanks
> Hari
> SQL Server MVP
> "Mehdi" <Mehdi@.discussions.microsoft.com> wrote in message
> news:E312383F-AB28-4B51-BE85-59B605BE118F@.microsoft.com...
|||You can achieve this security target only outside of SQL - restricting the
access to the detached database files. If someone can get access to those
files - he has access to the data inside, no way to prevent it.
Encryption of database files would be appropriate here - but it is not
available in SQL - only network communication and procedure bodies can be
encrypted.
Cheers
Wojtek
"Mehdi" <Mehdi@.discussions.microsoft.com> wrote in message
news:E312383F-AB28-4B51-BE85-59B605BE118F@.microsoft.com...
> Dear friends
> I have a question,please help me.
> I have a database and one of my database user is the owner of my database.
> If i detach my database , i can attach it on other instance of SQL SERVER
> with user of 'sa'.I want to stop this action in term of security.
> Thanks
> Mehdi
>

Database Owner

Dear friends
I have a question,please help me.
I have a database and one of my database user is the owner of my database.
If i detach my database , i can attach it on other instance of SQL SERVER
with user of 'sa'.I want to stop this action in term of security.
Thanks
MehdiHi,
SA is the system administrator and you cant restrict the permissions to
attach the database.
Thanks
Hari
SQL Server MVP
"Mehdi" <Mehdi@.discussions.microsoft.com> wrote in message
news:E312383F-AB28-4B51-BE85-59B605BE118F@.microsoft.com...
> Dear friends
> I have a question,please help me.
> I have a database and one of my database user is the owner of my database.
> If i detach my database , i can attach it on other instance of SQL SERVER
> with user of 'sa'.I want to stop this action in term of security.
> Thanks
> Mehdi
>|||However, AFTER you've re-attached the database you can change the
database owner using sp_changedbowner.
ALI
Hari Prasad wrote:[vbcol=seagreen]
> Hi,
> SA is the system administrator and you cant restrict the permissions to
> attach the database.
> Thanks
> Hari
> SQL Server MVP
> "Mehdi" <Mehdi@.discussions.microsoft.com> wrote in message
> news:E312383F-AB28-4B51-BE85-59B605BE118F@.microsoft.com...|||You can achieve this security target only outside of SQL - restricting the
access to the detached database files. If someone can get access to those
files - he has access to the data inside, no way to prevent it.
Encryption of database files would be appropriate here - but it is not
available in SQL - only network communication and procedure bodies can be
encrypted.
Cheers
Wojtek
"Mehdi" <Mehdi@.discussions.microsoft.com> wrote in message
news:E312383F-AB28-4B51-BE85-59B605BE118F@.microsoft.com...
> Dear friends
> I have a question,please help me.
> I have a database and one of my database user is the owner of my database.
> If i detach my database , i can attach it on other instance of SQL SERVER
> with user of 'sa'.I want to stop this action in term of security.
> Thanks
> Mehdi
>

Database Owner

Dear friends
I have a question,please help me.
I have a database and one of my database user is the owner of my database.
If i detach my database , i can attach it on other instance of SQL SERVER
with user of 'sa'.I want to stop this action in term of security.
Thanks
MehdiHi,
SA is the system administrator and you cant restrict the permissions to
attach the database.
Thanks
Hari
SQL Server MVP
"Mehdi" <Mehdi@.discussions.microsoft.com> wrote in message
news:E312383F-AB28-4B51-BE85-59B605BE118F@.microsoft.com...
> Dear friends
> I have a question,please help me.
> I have a database and one of my database user is the owner of my database.
> If i detach my database , i can attach it on other instance of SQL SERVER
> with user of 'sa'.I want to stop this action in term of security.
> Thanks
> Mehdi
>|||However, AFTER you've re-attached the database you can change the
database owner using sp_changedbowner.
ALI
Hari Prasad wrote:
> Hi,
> SA is the system administrator and you cant restrict the permissions to
> attach the database.
> Thanks
> Hari
> SQL Server MVP
> "Mehdi" <Mehdi@.discussions.microsoft.com> wrote in message
> news:E312383F-AB28-4B51-BE85-59B605BE118F@.microsoft.com...
> > Dear friends
> >
> > I have a question,please help me.
> > I have a database and one of my database user is the owner of my database.
> > If i detach my database , i can attach it on other instance of SQL SERVER
> > with user of 'sa'.I want to stop this action in term of security.
> >
> > Thanks
> > Mehdi
> >|||You can achieve this security target only outside of SQL - restricting the
access to the detached database files. If someone can get access to those
files - he has access to the data inside, no way to prevent it.
Encryption of database files would be appropriate here - but it is not
available in SQL - only network communication and procedure bodies can be
encrypted.
Cheers
Wojtek
"Mehdi" <Mehdi@.discussions.microsoft.com> wrote in message
news:E312383F-AB28-4B51-BE85-59B605BE118F@.microsoft.com...
> Dear friends
> I have a question,please help me.
> I have a database and one of my database user is the owner of my database.
> If i detach my database , i can attach it on other instance of SQL SERVER
> with user of 'sa'.I want to stop this action in term of security.
> Thanks
> Mehdi
>