I have two databases and one of them contains a table that
I would like to link between the two. I have used the link
table wizard to link a table from a secondary database
with Microsoft Access and was wondering if there is a way
to do this in SQL Server 2KThank you so very much!!!|||Try a View
CREATE VIEW DatabasenameN.Owner.VW_test
AS
SELECT a.V1, a.V2, b.V3 ......
FROM Databasename1.Owner.Table1 a
JOIN Databasename2.Owner.Table2 b on a.Vx = b.Vy
WHERE ......
Hope this Helps
Showing posts with label databases. Show all posts
Showing posts with label databases. Show all posts
Wednesday, March 28, 2012
Monday, March 12, 2012
Limits on Number of database...
I have created "around 25 excluding system databases" number of databases in
one SQL server instance. Is there any limits on number of SQL server
databases which affects the performance of SQL Server.
I am using SQL 2K.
Thanks,
Akshay
"Akshay Sen" <ASen89@.hotmail.com> wrote in message
news:eblLuwVCFHA.328@.tk2msftngp13.phx.gbl...
>I have created "around 25 excluding system databases" number of databases
>in
> one SQL server instance. Is there any limits on number of SQL server
> databases which affects the performance of SQL Server.
> I am using SQL 2K.
> Thanks,
> Akshay
>
It depends..
How heavily is each database used?
How big is your system (processors, memory etc.).
It's not really the number of databases that is an issue, but rather how
they are being used. One thing to note however is that with a lot of
databases, it may take longer for the SQL Server to come back online as it
does some work with each database when the system is restarted.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||Makes sense. Thanks
Akshay
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:#azT90VCFHA.520@.TK2MSFTNGP09.phx.gbl...
> "Akshay Sen" <ASen89@.hotmail.com> wrote in message
> news:eblLuwVCFHA.328@.tk2msftngp13.phx.gbl...
>
> It depends..
> How heavily is each database used?
> How big is your system (processors, memory etc.).
> It's not really the number of databases that is an issue, but rather how
> they are being used. One thing to note however is that with a lot of
> databases, it may take longer for the SQL Server to come back online as it
> does some work with each database when the system is restarted.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||Also keep in mind that the SQLServer:Database performance counters are only
collected on the first 99 databses.
Sincerely,
Anthony Thomas
"Akshay Sen" <ASen89@.hotmail.com> wrote in message
news:e1aaZ3VCFHA.868@.TK2MSFTNGP10.phx.gbl...
Makes sense. Thanks
Akshay
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:#azT90VCFHA.520@.TK2MSFTNGP09.phx.gbl...
> "Akshay Sen" <ASen89@.hotmail.com> wrote in message
> news:eblLuwVCFHA.328@.tk2msftngp13.phx.gbl...
>
> It depends..
> How heavily is each database used?
> How big is your system (processors, memory etc.).
> It's not really the number of databases that is an issue, but rather how
> they are being used. One thing to note however is that with a lot of
> databases, it may take longer for the SQL Server to come back online as it
> does some work with each database when the system is restarted.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
one SQL server instance. Is there any limits on number of SQL server
databases which affects the performance of SQL Server.
I am using SQL 2K.
Thanks,
Akshay
"Akshay Sen" <ASen89@.hotmail.com> wrote in message
news:eblLuwVCFHA.328@.tk2msftngp13.phx.gbl...
>I have created "around 25 excluding system databases" number of databases
>in
> one SQL server instance. Is there any limits on number of SQL server
> databases which affects the performance of SQL Server.
> I am using SQL 2K.
> Thanks,
> Akshay
>
It depends..
How heavily is each database used?
How big is your system (processors, memory etc.).
It's not really the number of databases that is an issue, but rather how
they are being used. One thing to note however is that with a lot of
databases, it may take longer for the SQL Server to come back online as it
does some work with each database when the system is restarted.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||Makes sense. Thanks
Akshay
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:#azT90VCFHA.520@.TK2MSFTNGP09.phx.gbl...
> "Akshay Sen" <ASen89@.hotmail.com> wrote in message
> news:eblLuwVCFHA.328@.tk2msftngp13.phx.gbl...
>
> It depends..
> How heavily is each database used?
> How big is your system (processors, memory etc.).
> It's not really the number of databases that is an issue, but rather how
> they are being used. One thing to note however is that with a lot of
> databases, it may take longer for the SQL Server to come back online as it
> does some work with each database when the system is restarted.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||Also keep in mind that the SQLServer:Database performance counters are only
collected on the first 99 databses.
Sincerely,
Anthony Thomas
"Akshay Sen" <ASen89@.hotmail.com> wrote in message
news:e1aaZ3VCFHA.868@.TK2MSFTNGP10.phx.gbl...
Makes sense. Thanks
Akshay
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:#azT90VCFHA.520@.TK2MSFTNGP09.phx.gbl...
> "Akshay Sen" <ASen89@.hotmail.com> wrote in message
> news:eblLuwVCFHA.328@.tk2msftngp13.phx.gbl...
>
> It depends..
> How heavily is each database used?
> How big is your system (processors, memory etc.).
> It's not really the number of databases that is an issue, but rather how
> they are being used. One thing to note however is that with a lot of
> databases, it may take longer for the SQL Server to come back online as it
> does some work with each database when the system is restarted.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
Limits on Number of database...
I have created "around 25 excluding system databases" number of databases in
one SQL server instance. Is there any limits on number of SQL server
databases which affects the performance of SQL Server.
I am using SQL 2K.
Thanks,
Akshay"Akshay Sen" <ASen89@.hotmail.com> wrote in message
news:eblLuwVCFHA.328@.tk2msftngp13.phx.gbl...
>I have created "around 25 excluding system databases" number of databases
>in
> one SQL server instance. Is there any limits on number of SQL server
> databases which affects the performance of SQL Server.
> I am using SQL 2K.
> Thanks,
> Akshay
>
It depends..
How heavily is each database used?
How big is your system (processors, memory etc.).
It's not really the number of databases that is an issue, but rather how
they are being used. One thing to note however is that with a lot of
databases, it may take longer for the SQL Server to come back online as it
does some work with each database when the system is restarted.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Makes sense. Thanks
Akshay
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:#azT90VCFHA.520@.TK2MSFTNGP09.phx.gbl...
> "Akshay Sen" <ASen89@.hotmail.com> wrote in message
> news:eblLuwVCFHA.328@.tk2msftngp13.phx.gbl...
>
> It depends..
> How heavily is each database used?
> How big is your system (processors, memory etc.).
> It's not really the number of databases that is an issue, but rather how
> they are being used. One thing to note however is that with a lot of
> databases, it may take longer for the SQL Server to come back online as it
> does some work with each database when the system is restarted.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Also keep in mind that the SQLServer:Database performance counters are only
collected on the first 99 databses.
Sincerely,
Anthony Thomas
"Akshay Sen" <ASen89@.hotmail.com> wrote in message
news:e1aaZ3VCFHA.868@.TK2MSFTNGP10.phx.gbl...
Makes sense. Thanks
Akshay
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:#azT90VCFHA.520@.TK2MSFTNGP09.phx.gbl...
> "Akshay Sen" <ASen89@.hotmail.com> wrote in message
> news:eblLuwVCFHA.328@.tk2msftngp13.phx.gbl...
>
> It depends..
> How heavily is each database used?
> How big is your system (processors, memory etc.).
> It's not really the number of databases that is an issue, but rather how
> they are being used. One thing to note however is that with a lot of
> databases, it may take longer for the SQL Server to come back online as it
> does some work with each database when the system is restarted.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
one SQL server instance. Is there any limits on number of SQL server
databases which affects the performance of SQL Server.
I am using SQL 2K.
Thanks,
Akshay"Akshay Sen" <ASen89@.hotmail.com> wrote in message
news:eblLuwVCFHA.328@.tk2msftngp13.phx.gbl...
>I have created "around 25 excluding system databases" number of databases
>in
> one SQL server instance. Is there any limits on number of SQL server
> databases which affects the performance of SQL Server.
> I am using SQL 2K.
> Thanks,
> Akshay
>
It depends..
How heavily is each database used?
How big is your system (processors, memory etc.).
It's not really the number of databases that is an issue, but rather how
they are being used. One thing to note however is that with a lot of
databases, it may take longer for the SQL Server to come back online as it
does some work with each database when the system is restarted.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Makes sense. Thanks
Akshay
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:#azT90VCFHA.520@.TK2MSFTNGP09.phx.gbl...
> "Akshay Sen" <ASen89@.hotmail.com> wrote in message
> news:eblLuwVCFHA.328@.tk2msftngp13.phx.gbl...
>
> It depends..
> How heavily is each database used?
> How big is your system (processors, memory etc.).
> It's not really the number of databases that is an issue, but rather how
> they are being used. One thing to note however is that with a lot of
> databases, it may take longer for the SQL Server to come back online as it
> does some work with each database when the system is restarted.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Also keep in mind that the SQLServer:Database performance counters are only
collected on the first 99 databses.
Sincerely,
Anthony Thomas
"Akshay Sen" <ASen89@.hotmail.com> wrote in message
news:e1aaZ3VCFHA.868@.TK2MSFTNGP10.phx.gbl...
Makes sense. Thanks
Akshay
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:#azT90VCFHA.520@.TK2MSFTNGP09.phx.gbl...
> "Akshay Sen" <ASen89@.hotmail.com> wrote in message
> news:eblLuwVCFHA.328@.tk2msftngp13.phx.gbl...
>
> It depends..
> How heavily is each database used?
> How big is your system (processors, memory etc.).
> It's not really the number of databases that is an issue, but rather how
> they are being used. One thing to note however is that with a lot of
> databases, it may take longer for the SQL Server to come back online as it
> does some work with each database when the system is restarted.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
Limits on Number of database...
I have created "around 25 excluding system databases" number of databases in
one SQL server instance. Is there any limits on number of SQL server
databases which affects the performance of SQL Server.
I am using SQL 2K.
Thanks,
Akshay"Akshay Sen" <ASen89@.hotmail.com> wrote in message
news:eblLuwVCFHA.328@.tk2msftngp13.phx.gbl...
>I have created "around 25 excluding system databases" number of databases
>in
> one SQL server instance. Is there any limits on number of SQL server
> databases which affects the performance of SQL Server.
> I am using SQL 2K.
> Thanks,
> Akshay
>
It depends..
How heavily is each database used?
How big is your system (processors, memory etc.).
It's not really the number of databases that is an issue, but rather how
they are being used. One thing to note however is that with a lot of
databases, it may take longer for the SQL Server to come back online as it
does some work with each database when the system is restarted.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Makes sense. Thanks
Akshay
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:#azT90VCFHA.520@.TK2MSFTNGP09.phx.gbl...
> "Akshay Sen" <ASen89@.hotmail.com> wrote in message
> news:eblLuwVCFHA.328@.tk2msftngp13.phx.gbl...
> >I have created "around 25 excluding system databases" number of databases
> >in
> > one SQL server instance. Is there any limits on number of SQL server
> > databases which affects the performance of SQL Server.
> > I am using SQL 2K.
> > Thanks,
> > Akshay
> >
> >
>
> It depends..
> How heavily is each database used?
> How big is your system (processors, memory etc.).
> It's not really the number of databases that is an issue, but rather how
> they are being used. One thing to note however is that with a lot of
> databases, it may take longer for the SQL Server to come back online as it
> does some work with each database when the system is restarted.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Also keep in mind that the SQLServer:Database performance counters are only
collected on the first 99 databses.
Sincerely,
Anthony Thomas
"Akshay Sen" <ASen89@.hotmail.com> wrote in message
news:e1aaZ3VCFHA.868@.TK2MSFTNGP10.phx.gbl...
Makes sense. Thanks
Akshay
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:#azT90VCFHA.520@.TK2MSFTNGP09.phx.gbl...
> "Akshay Sen" <ASen89@.hotmail.com> wrote in message
> news:eblLuwVCFHA.328@.tk2msftngp13.phx.gbl...
> >I have created "around 25 excluding system databases" number of databases
> >in
> > one SQL server instance. Is there any limits on number of SQL server
> > databases which affects the performance of SQL Server.
> > I am using SQL 2K.
> > Thanks,
> > Akshay
> >
> >
>
> It depends..
> How heavily is each database used?
> How big is your system (processors, memory etc.).
> It's not really the number of databases that is an issue, but rather how
> they are being used. One thing to note however is that with a lot of
> databases, it may take longer for the SQL Server to come back online as it
> does some work with each database when the system is restarted.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
one SQL server instance. Is there any limits on number of SQL server
databases which affects the performance of SQL Server.
I am using SQL 2K.
Thanks,
Akshay"Akshay Sen" <ASen89@.hotmail.com> wrote in message
news:eblLuwVCFHA.328@.tk2msftngp13.phx.gbl...
>I have created "around 25 excluding system databases" number of databases
>in
> one SQL server instance. Is there any limits on number of SQL server
> databases which affects the performance of SQL Server.
> I am using SQL 2K.
> Thanks,
> Akshay
>
It depends..
How heavily is each database used?
How big is your system (processors, memory etc.).
It's not really the number of databases that is an issue, but rather how
they are being used. One thing to note however is that with a lot of
databases, it may take longer for the SQL Server to come back online as it
does some work with each database when the system is restarted.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Makes sense. Thanks
Akshay
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:#azT90VCFHA.520@.TK2MSFTNGP09.phx.gbl...
> "Akshay Sen" <ASen89@.hotmail.com> wrote in message
> news:eblLuwVCFHA.328@.tk2msftngp13.phx.gbl...
> >I have created "around 25 excluding system databases" number of databases
> >in
> > one SQL server instance. Is there any limits on number of SQL server
> > databases which affects the performance of SQL Server.
> > I am using SQL 2K.
> > Thanks,
> > Akshay
> >
> >
>
> It depends..
> How heavily is each database used?
> How big is your system (processors, memory etc.).
> It's not really the number of databases that is an issue, but rather how
> they are being used. One thing to note however is that with a lot of
> databases, it may take longer for the SQL Server to come back online as it
> does some work with each database when the system is restarted.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Also keep in mind that the SQLServer:Database performance counters are only
collected on the first 99 databses.
Sincerely,
Anthony Thomas
"Akshay Sen" <ASen89@.hotmail.com> wrote in message
news:e1aaZ3VCFHA.868@.TK2MSFTNGP10.phx.gbl...
Makes sense. Thanks
Akshay
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:#azT90VCFHA.520@.TK2MSFTNGP09.phx.gbl...
> "Akshay Sen" <ASen89@.hotmail.com> wrote in message
> news:eblLuwVCFHA.328@.tk2msftngp13.phx.gbl...
> >I have created "around 25 excluding system databases" number of databases
> >in
> > one SQL server instance. Is there any limits on number of SQL server
> > databases which affects the performance of SQL Server.
> > I am using SQL 2K.
> > Thanks,
> > Akshay
> >
> >
>
> It depends..
> How heavily is each database used?
> How big is your system (processors, memory etc.).
> It's not really the number of databases that is an issue, but rather how
> they are being used. One thing to note however is that with a lot of
> databases, it may take longer for the SQL Server to come back online as it
> does some work with each database when the system is restarted.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
Limiting users to see particular databases in SSMS
Hi,
For a web hosting company, I need to grant access to users the way that when
they connect to SQL Server using SSMS, they must only be able to see their
own databases.
I tried to revoke VIEW ANY DATABASE that caused list of databases disappear
from SSMS, but how can I turn back particular database for a user?
Thanks in advance,
Leila
To limit visibility to database metadata, deny a login the VIEW ANY DATABASE
permission. After this permission is denied, a login can see only metadata
for master, tempdb, and databases that it owns.
BOL Ref:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/88ab9837-b707-43a9-8926-3e964f5a8b08.htm
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Leila" wrote:
> Hi,
> For a web hosting company, I need to grant access to users the way that when
> they connect to SQL Server using SSMS, they must only be able to see their
> own databases.
> I tried to revoke VIEW ANY DATABASE that caused list of databases disappear
> from SSMS, but how can I turn back particular database for a user?
> Thanks in advance,
> Leila
>
>
|||Hi Mohit,
The visibility of metadata is correct, but their own database does not
appear in SSMS (Object Explorer)
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:0BE934B1-0729-462C-8D98-B3540E5BC21B@.microsoft.com...[vbcol=seagreen]
> To limit visibility to database metadata, deny a login the VIEW ANY
> DATABASE
> permission. After this permission is denied, a login can see only metadata
> for master, tempdb, and databases that it owns.
> BOL Ref:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/88ab9837-b707-43a9-8926-3e964f5a8b08.htm
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "Leila" wrote:
|||Leila (Leilas@.hotpop.com) writes:
> Hi Mohit,
> The visibility of metadata is correct, but their own database does not
> appear in SSMS (Object Explorer)
Do they actually own the databases, or the databases theirs by some tacit
agreement.
Try this:
CREATE LOGIN erik WITH PASSWORD='rtsoppa'
go
CREATE DATABASE eriks
ALTER AUTHORIZATION ON DATABASE::eriks TO erik
go
EXECUTE AS login = 'erik'
go
SELECT name FROM sys.databases
go
REVERT
go
DENY VIEW ANY DATABASE TO erik
go
EXECUTE AS login = 'erik'
go
SELECT name FROM sys.databases
go
REVERT
go
DROP DATABASE eriks
go
DROP LOGIN erik
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||As Erland said the user must be db_Owner or it will not work. I tested this
in SQL 2005 SP2. Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
|||Leila
This should work if the user is the owner of the database.
Create a new SQL login "login1"
Create a user named login1 in master database
Grant CREATE DATABASE to login1
While impersonating login1, create a database called dbteste
Revoke CREATE DATABASE permission from login1
Revoke VIEW ANY DATABASE permission from PUBLIC
Register this server as login1
From the login1 session, expand database tree. Now, you should see
master, tempdb, dbteste
Grant VIEW ANY DATABASE to PUBLIC
From the login1 session, you should see all the databases
"Leila" <Leilas@.hotpop.com> wrote in message
news:uKzGwhBwHHA.4184@.TK2MSFTNGP06.phx.gbl...
> Hi,
> For a web hosting company, I need to grant access to users the way that
> when they connect to SQL Server using SSMS, they must only be able to see
> their own databases.
> I tried to revoke VIEW ANY DATABASE that caused list of databases
> disappear from SSMS, but how can I turn back particular database for a
> user?
> Thanks in advance,
> Leila
>
|||Thanks everybody!
It seems that it doesn't work for membership of the user in db_owner
database role. The login must be the owner of database, is that true?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eJ$Q76SwHHA.1524@.TK2MSFTNGP06.phx.gbl...
> Leila
> This should work if the user is the owner of the database.
> Create a new SQL login "login1"
> Create a user named login1 in master database
> Grant CREATE DATABASE to login1
> While impersonating login1, create a database called dbteste
> Revoke CREATE DATABASE permission from login1
> Revoke VIEW ANY DATABASE permission from PUBLIC
> Register this server as login1
> From the login1 session, expand database tree. Now, you should see
> master, tempdb, dbteste
> Grant VIEW ANY DATABASE to PUBLIC
> From the login1 session, you should see all the databases
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uKzGwhBwHHA.4184@.TK2MSFTNGP06.phx.gbl...
>
|||Leila (Leilas@.hotpop.com) writes:
> It seems that it doesn't work for membership of the user in db_owner
> database role. The login must be the owner of database, is that true?
Yes, that's the way it is. To determine whether a user is entitled to
see the definition of a certain database, SQL Server looks at server-
level information only. Whether a user may be part of the db_owner role
in a certain database, would reqiure SQL Server to peek into that database,
and this could be very costly if there are many databases on the server,
and particularly if they are set to autoclose.
I have a suggestion on Connect about this:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=273830
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
For a web hosting company, I need to grant access to users the way that when
they connect to SQL Server using SSMS, they must only be able to see their
own databases.
I tried to revoke VIEW ANY DATABASE that caused list of databases disappear
from SSMS, but how can I turn back particular database for a user?
Thanks in advance,
Leila
To limit visibility to database metadata, deny a login the VIEW ANY DATABASE
permission. After this permission is denied, a login can see only metadata
for master, tempdb, and databases that it owns.
BOL Ref:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/88ab9837-b707-43a9-8926-3e964f5a8b08.htm
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Leila" wrote:
> Hi,
> For a web hosting company, I need to grant access to users the way that when
> they connect to SQL Server using SSMS, they must only be able to see their
> own databases.
> I tried to revoke VIEW ANY DATABASE that caused list of databases disappear
> from SSMS, but how can I turn back particular database for a user?
> Thanks in advance,
> Leila
>
>
|||Hi Mohit,
The visibility of metadata is correct, but their own database does not
appear in SSMS (Object Explorer)
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:0BE934B1-0729-462C-8D98-B3540E5BC21B@.microsoft.com...[vbcol=seagreen]
> To limit visibility to database metadata, deny a login the VIEW ANY
> DATABASE
> permission. After this permission is denied, a login can see only metadata
> for master, tempdb, and databases that it owns.
> BOL Ref:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/88ab9837-b707-43a9-8926-3e964f5a8b08.htm
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "Leila" wrote:
|||Leila (Leilas@.hotpop.com) writes:
> Hi Mohit,
> The visibility of metadata is correct, but their own database does not
> appear in SSMS (Object Explorer)
Do they actually own the databases, or the databases theirs by some tacit
agreement.
Try this:
CREATE LOGIN erik WITH PASSWORD='rtsoppa'
go
CREATE DATABASE eriks
ALTER AUTHORIZATION ON DATABASE::eriks TO erik
go
EXECUTE AS login = 'erik'
go
SELECT name FROM sys.databases
go
REVERT
go
DENY VIEW ANY DATABASE TO erik
go
EXECUTE AS login = 'erik'
go
SELECT name FROM sys.databases
go
REVERT
go
DROP DATABASE eriks
go
DROP LOGIN erik
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||As Erland said the user must be db_Owner or it will not work. I tested this
in SQL 2005 SP2. Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
|||Leila
This should work if the user is the owner of the database.
Create a new SQL login "login1"
Create a user named login1 in master database
Grant CREATE DATABASE to login1
While impersonating login1, create a database called dbteste
Revoke CREATE DATABASE permission from login1
Revoke VIEW ANY DATABASE permission from PUBLIC
Register this server as login1
From the login1 session, expand database tree. Now, you should see
master, tempdb, dbteste
Grant VIEW ANY DATABASE to PUBLIC
From the login1 session, you should see all the databases
"Leila" <Leilas@.hotpop.com> wrote in message
news:uKzGwhBwHHA.4184@.TK2MSFTNGP06.phx.gbl...
> Hi,
> For a web hosting company, I need to grant access to users the way that
> when they connect to SQL Server using SSMS, they must only be able to see
> their own databases.
> I tried to revoke VIEW ANY DATABASE that caused list of databases
> disappear from SSMS, but how can I turn back particular database for a
> user?
> Thanks in advance,
> Leila
>
|||Thanks everybody!
It seems that it doesn't work for membership of the user in db_owner
database role. The login must be the owner of database, is that true?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eJ$Q76SwHHA.1524@.TK2MSFTNGP06.phx.gbl...
> Leila
> This should work if the user is the owner of the database.
> Create a new SQL login "login1"
> Create a user named login1 in master database
> Grant CREATE DATABASE to login1
> While impersonating login1, create a database called dbteste
> Revoke CREATE DATABASE permission from login1
> Revoke VIEW ANY DATABASE permission from PUBLIC
> Register this server as login1
> From the login1 session, expand database tree. Now, you should see
> master, tempdb, dbteste
> Grant VIEW ANY DATABASE to PUBLIC
> From the login1 session, you should see all the databases
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uKzGwhBwHHA.4184@.TK2MSFTNGP06.phx.gbl...
>
|||Leila (Leilas@.hotpop.com) writes:
> It seems that it doesn't work for membership of the user in db_owner
> database role. The login must be the owner of database, is that true?
Yes, that's the way it is. To determine whether a user is entitled to
see the definition of a certain database, SQL Server looks at server-
level information only. Whether a user may be part of the db_owner role
in a certain database, would reqiure SQL Server to peek into that database,
and this could be very costly if there are many databases on the server,
and particularly if they are set to autoclose.
I have a suggestion on Connect about this:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=273830
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Limiting users to see particular databases in SSMS
Hi,
For a web hosting company, I need to grant access to users the way that when
they connect to SQL Server using SSMS, they must only be able to see their
own databases.
I tried to revoke VIEW ANY DATABASE that caused list of databases disappear
from SSMS, but how can I turn back particular database for a user?
Thanks in advance,
LeilaTo limit visibility to database metadata, deny a login the VIEW ANY DATABASE
permission. After this permission is denied, a login can see only metadata
for master, tempdb, and databases that it owns.
BOL Ref:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/88ab9837-b707-43a9-8926-3e96
4f5a8b08.htm
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Leila" wrote:
> Hi,
> For a web hosting company, I need to grant access to users the way that wh
en
> they connect to SQL Server using SSMS, they must only be able to see their
> own databases.
> I tried to revoke VIEW ANY DATABASE that caused list of databases disappea
r
> from SSMS, but how can I turn back particular database for a user?
> Thanks in advance,
> Leila
>
>|||Hi Mohit,
The visibility of metadata is correct, but their own database does not
appear in SSMS (Object Explorer)
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:0BE934B1-0729-462C-8D98-B3540E5BC21B@.microsoft.com...[vbcol=seagreen]
> To limit visibility to database metadata, deny a login the VIEW ANY
> DATABASE
> permission. After this permission is denied, a login can see only metadata
> for master, tempdb, and databases that it owns.
> BOL Ref:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/88ab9837-b707-43a9-8926-3e
964f5a8b08.htm
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "Leila" wrote:
>|||Leila (Leilas@.hotpop.com) writes:
> Hi Mohit,
> The visibility of metadata is correct, but their own database does not
> appear in SSMS (Object Explorer)
Do they actually own the databases, or the databases theirs by some tacit
agreement.
Try this:
CREATE LOGIN erik WITH PASSWORD='rtsoppa'
go
CREATE DATABASE eriks
ALTER AUTHORIZATION ON DATABASE::eriks TO erik
go
EXECUTE AS login = 'erik'
go
SELECT name FROM sys.databases
go
REVERT
go
DENY VIEW ANY DATABASE TO erik
go
EXECUTE AS login = 'erik'
go
SELECT name FROM sys.databases
go
REVERT
go
DROP DATABASE eriks
go
DROP LOGIN erik
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|||As Erland said the user must be db_Owner or it will not work. I tested this
in SQL 2005 SP2. Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005|||Leila
This should work if the user is the owner of the database.
Create a new SQL login "login1"
Create a user named login1 in master database
Grant CREATE DATABASE to login1
While impersonating login1, create a database called dbteste
Revoke CREATE DATABASE permission from login1
Revoke VIEW ANY DATABASE permission from PUBLIC
Register this server as login1
From the login1 session, expand database tree. Now, you should see
master, tempdb, dbteste
Grant VIEW ANY DATABASE to PUBLIC
From the login1 session, you should see all the databases
"Leila" <Leilas@.hotpop.com> wrote in message
news:uKzGwhBwHHA.4184@.TK2MSFTNGP06.phx.gbl...
> Hi,
> For a web hosting company, I need to grant access to users the way that
> when they connect to SQL Server using SSMS, they must only be able to see
> their own databases.
> I tried to revoke VIEW ANY DATABASE that caused list of databases
> disappear from SSMS, but how can I turn back particular database for a
> user?
> Thanks in advance,
> Leila
>|||Thanks everybody!
It seems that it doesn't work for membership of the user in db_owner
database role. The login must be the owner of database, is that true?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eJ$Q76SwHHA.1524@.TK2MSFTNGP06.phx.gbl...
> Leila
> This should work if the user is the owner of the database.
> Create a new SQL login "login1"
> Create a user named login1 in master database
> Grant CREATE DATABASE to login1
> While impersonating login1, create a database called dbteste
> Revoke CREATE DATABASE permission from login1
> Revoke VIEW ANY DATABASE permission from PUBLIC
> Register this server as login1
> From the login1 session, expand database tree. Now, you should see
> master, tempdb, dbteste
> Grant VIEW ANY DATABASE to PUBLIC
> From the login1 session, you should see all the databases
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uKzGwhBwHHA.4184@.TK2MSFTNGP06.phx.gbl...
>|||Leila (Leilas@.hotpop.com) writes:
> It seems that it doesn't work for membership of the user in db_owner
> database role. The login must be the owner of database, is that true?
Yes, that's the way it is. To determine whether a user is entitled to
see the definition of a certain database, SQL Server looks at server-
level information only. Whether a user may be part of the db_owner role
in a certain database, would reqiure SQL Server to peek into that database,
and this could be very costly if there are many databases on the server,
and particularly if they are set to autoclose.
I have a suggestion on Connect about this:
https://connect.microsoft.com/SQLSe...=2738
30
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
For a web hosting company, I need to grant access to users the way that when
they connect to SQL Server using SSMS, they must only be able to see their
own databases.
I tried to revoke VIEW ANY DATABASE that caused list of databases disappear
from SSMS, but how can I turn back particular database for a user?
Thanks in advance,
LeilaTo limit visibility to database metadata, deny a login the VIEW ANY DATABASE
permission. After this permission is denied, a login can see only metadata
for master, tempdb, and databases that it owns.
BOL Ref:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/88ab9837-b707-43a9-8926-3e96
4f5a8b08.htm
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Leila" wrote:
> Hi,
> For a web hosting company, I need to grant access to users the way that wh
en
> they connect to SQL Server using SSMS, they must only be able to see their
> own databases.
> I tried to revoke VIEW ANY DATABASE that caused list of databases disappea
r
> from SSMS, but how can I turn back particular database for a user?
> Thanks in advance,
> Leila
>
>|||Hi Mohit,
The visibility of metadata is correct, but their own database does not
appear in SSMS (Object Explorer)
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:0BE934B1-0729-462C-8D98-B3540E5BC21B@.microsoft.com...[vbcol=seagreen]
> To limit visibility to database metadata, deny a login the VIEW ANY
> DATABASE
> permission. After this permission is denied, a login can see only metadata
> for master, tempdb, and databases that it owns.
> BOL Ref:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/88ab9837-b707-43a9-8926-3e
964f5a8b08.htm
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "Leila" wrote:
>|||Leila (Leilas@.hotpop.com) writes:
> Hi Mohit,
> The visibility of metadata is correct, but their own database does not
> appear in SSMS (Object Explorer)
Do they actually own the databases, or the databases theirs by some tacit
agreement.
Try this:
CREATE LOGIN erik WITH PASSWORD='rtsoppa'
go
CREATE DATABASE eriks
ALTER AUTHORIZATION ON DATABASE::eriks TO erik
go
EXECUTE AS login = 'erik'
go
SELECT name FROM sys.databases
go
REVERT
go
DENY VIEW ANY DATABASE TO erik
go
EXECUTE AS login = 'erik'
go
SELECT name FROM sys.databases
go
REVERT
go
DROP DATABASE eriks
go
DROP LOGIN erik
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|||As Erland said the user must be db_Owner or it will not work. I tested this
in SQL 2005 SP2. Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005|||Leila
This should work if the user is the owner of the database.
Create a new SQL login "login1"
Create a user named login1 in master database
Grant CREATE DATABASE to login1
While impersonating login1, create a database called dbteste
Revoke CREATE DATABASE permission from login1
Revoke VIEW ANY DATABASE permission from PUBLIC
Register this server as login1
From the login1 session, expand database tree. Now, you should see
master, tempdb, dbteste
Grant VIEW ANY DATABASE to PUBLIC
From the login1 session, you should see all the databases
"Leila" <Leilas@.hotpop.com> wrote in message
news:uKzGwhBwHHA.4184@.TK2MSFTNGP06.phx.gbl...
> Hi,
> For a web hosting company, I need to grant access to users the way that
> when they connect to SQL Server using SSMS, they must only be able to see
> their own databases.
> I tried to revoke VIEW ANY DATABASE that caused list of databases
> disappear from SSMS, but how can I turn back particular database for a
> user?
> Thanks in advance,
> Leila
>|||Thanks everybody!
It seems that it doesn't work for membership of the user in db_owner
database role. The login must be the owner of database, is that true?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eJ$Q76SwHHA.1524@.TK2MSFTNGP06.phx.gbl...
> Leila
> This should work if the user is the owner of the database.
> Create a new SQL login "login1"
> Create a user named login1 in master database
> Grant CREATE DATABASE to login1
> While impersonating login1, create a database called dbteste
> Revoke CREATE DATABASE permission from login1
> Revoke VIEW ANY DATABASE permission from PUBLIC
> Register this server as login1
> From the login1 session, expand database tree. Now, you should see
> master, tempdb, dbteste
> Grant VIEW ANY DATABASE to PUBLIC
> From the login1 session, you should see all the databases
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uKzGwhBwHHA.4184@.TK2MSFTNGP06.phx.gbl...
>|||Leila (Leilas@.hotpop.com) writes:
> It seems that it doesn't work for membership of the user in db_owner
> database role. The login must be the owner of database, is that true?
Yes, that's the way it is. To determine whether a user is entitled to
see the definition of a certain database, SQL Server looks at server-
level information only. Whether a user may be part of the db_owner role
in a certain database, would reqiure SQL Server to peek into that database,
and this could be very costly if there are many databases on the server,
and particularly if they are set to autoclose.
I have a suggestion on Connect about this:
https://connect.microsoft.com/SQLSe...=2738
30
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
Limiting users to see particular databases in SSMS
Hi,
For a web hosting company, I need to grant access to users the way that when
they connect to SQL Server using SSMS, they must only be able to see their
own databases.
I tried to revoke VIEW ANY DATABASE that caused list of databases disappear
from SSMS, but how can I turn back particular database for a user?
Thanks in advance,
LeilaTo limit visibility to database metadata, deny a login the VIEW ANY DATABASE
permission. After this permission is denied, a login can see only metadata
for master, tempdb, and databases that it owns.
BOL Ref:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/88ab9837-b707-43a9-8926-3e964f5a8b08.htm
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Leila" wrote:
> Hi,
> For a web hosting company, I need to grant access to users the way that when
> they connect to SQL Server using SSMS, they must only be able to see their
> own databases.
> I tried to revoke VIEW ANY DATABASE that caused list of databases disappear
> from SSMS, but how can I turn back particular database for a user?
> Thanks in advance,
> Leila
>
>|||Hi Mohit,
The visibility of metadata is correct, but their own database does not
appear in SSMS (Object Explorer)
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:0BE934B1-0729-462C-8D98-B3540E5BC21B@.microsoft.com...
> To limit visibility to database metadata, deny a login the VIEW ANY
> DATABASE
> permission. After this permission is denied, a login can see only metadata
> for master, tempdb, and databases that it owns.
> BOL Ref:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/88ab9837-b707-43a9-8926-3e964f5a8b08.htm
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "Leila" wrote:
>> Hi,
>> For a web hosting company, I need to grant access to users the way that
>> when
>> they connect to SQL Server using SSMS, they must only be able to see
>> their
>> own databases.
>> I tried to revoke VIEW ANY DATABASE that caused list of databases
>> disappear
>> from SSMS, but how can I turn back particular database for a user?
>> Thanks in advance,
>> Leila
>>|||Leila (Leilas@.hotpop.com) writes:
> Hi Mohit,
> The visibility of metadata is correct, but their own database does not
> appear in SSMS (Object Explorer)
Do they actually own the databases, or the databases theirs by some tacit
agreement.
Try this:
CREATE LOGIN erik WITH PASSWORD='ärtsoppa'
go
CREATE DATABASE eriks
ALTER AUTHORIZATION ON DATABASE::eriks TO erik
go
EXECUTE AS login = 'erik'
go
SELECT name FROM sys.databases
go
REVERT
go
DENY VIEW ANY DATABASE TO erik
go
EXECUTE AS login = 'erik'
go
SELECT name FROM sys.databases
go
REVERT
go
DROP DATABASE eriks
go
DROP LOGIN erik
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||As Erland said the user must be db_Owner or it will not work. I tested this
in SQL 2005 SP2. Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005|||Leila
This should work if the user is the owner of the database.
? Create a new SQL login "login1"
? Create a user named ?login1? in master database
? Grant CREATE DATABASE to login1
? While impersonating login1, create a database called ?dbteste?
? Revoke CREATE DATABASE permission from login1
? Revoke VIEW ANY DATABASE permission from PUBLIC
? Register this server as login1
? From the ?login1? session, expand database tree. Now, you should see
master, tempdb, dbteste
? Grant VIEW ANY DATABASE to PUBLIC
? From the ?login1? session, you should see all the databases
"Leila" <Leilas@.hotpop.com> wrote in message
news:uKzGwhBwHHA.4184@.TK2MSFTNGP06.phx.gbl...
> Hi,
> For a web hosting company, I need to grant access to users the way that
> when they connect to SQL Server using SSMS, they must only be able to see
> their own databases.
> I tried to revoke VIEW ANY DATABASE that caused list of databases
> disappear from SSMS, but how can I turn back particular database for a
> user?
> Thanks in advance,
> Leila
>|||Thanks everybody!
It seems that it doesn't work for membership of the user in db_owner
database role. The login must be the owner of database, is that true?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eJ$Q76SwHHA.1524@.TK2MSFTNGP06.phx.gbl...
> Leila
> This should work if the user is the owner of the database.
> ? Create a new SQL login "login1"
> ? Create a user named ?login1? in master database
> ? Grant CREATE DATABASE to login1
> ? While impersonating login1, create a database called ?dbteste?
> ? Revoke CREATE DATABASE permission from login1
> ? Revoke VIEW ANY DATABASE permission from PUBLIC
> ? Register this server as login1
> ? From the ?login1? session, expand database tree. Now, you should see
> master, tempdb, dbteste
> ? Grant VIEW ANY DATABASE to PUBLIC
> ? From the ?login1? session, you should see all the databases
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uKzGwhBwHHA.4184@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> For a web hosting company, I need to grant access to users the way that
>> when they connect to SQL Server using SSMS, they must only be able to see
>> their own databases.
>> I tried to revoke VIEW ANY DATABASE that caused list of databases
>> disappear from SSMS, but how can I turn back particular database for a
>> user?
>> Thanks in advance,
>> Leila
>|||Leila (Leilas@.hotpop.com) writes:
> It seems that it doesn't work for membership of the user in db_owner
> database role. The login must be the owner of database, is that true?
Yes, that's the way it is. To determine whether a user is entitled to
see the definition of a certain database, SQL Server looks at server-
level information only. Whether a user may be part of the db_owner role
in a certain database, would reqiure SQL Server to peek into that database,
and this could be very costly if there are many databases on the server,
and particularly if they are set to autoclose.
I have a suggestion on Connect about this:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=273830
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
For a web hosting company, I need to grant access to users the way that when
they connect to SQL Server using SSMS, they must only be able to see their
own databases.
I tried to revoke VIEW ANY DATABASE that caused list of databases disappear
from SSMS, but how can I turn back particular database for a user?
Thanks in advance,
LeilaTo limit visibility to database metadata, deny a login the VIEW ANY DATABASE
permission. After this permission is denied, a login can see only metadata
for master, tempdb, and databases that it owns.
BOL Ref:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/88ab9837-b707-43a9-8926-3e964f5a8b08.htm
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Leila" wrote:
> Hi,
> For a web hosting company, I need to grant access to users the way that when
> they connect to SQL Server using SSMS, they must only be able to see their
> own databases.
> I tried to revoke VIEW ANY DATABASE that caused list of databases disappear
> from SSMS, but how can I turn back particular database for a user?
> Thanks in advance,
> Leila
>
>|||Hi Mohit,
The visibility of metadata is correct, but their own database does not
appear in SSMS (Object Explorer)
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:0BE934B1-0729-462C-8D98-B3540E5BC21B@.microsoft.com...
> To limit visibility to database metadata, deny a login the VIEW ANY
> DATABASE
> permission. After this permission is denied, a login can see only metadata
> for master, tempdb, and databases that it owns.
> BOL Ref:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/88ab9837-b707-43a9-8926-3e964f5a8b08.htm
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "Leila" wrote:
>> Hi,
>> For a web hosting company, I need to grant access to users the way that
>> when
>> they connect to SQL Server using SSMS, they must only be able to see
>> their
>> own databases.
>> I tried to revoke VIEW ANY DATABASE that caused list of databases
>> disappear
>> from SSMS, but how can I turn back particular database for a user?
>> Thanks in advance,
>> Leila
>>|||Leila (Leilas@.hotpop.com) writes:
> Hi Mohit,
> The visibility of metadata is correct, but their own database does not
> appear in SSMS (Object Explorer)
Do they actually own the databases, or the databases theirs by some tacit
agreement.
Try this:
CREATE LOGIN erik WITH PASSWORD='ärtsoppa'
go
CREATE DATABASE eriks
ALTER AUTHORIZATION ON DATABASE::eriks TO erik
go
EXECUTE AS login = 'erik'
go
SELECT name FROM sys.databases
go
REVERT
go
DENY VIEW ANY DATABASE TO erik
go
EXECUTE AS login = 'erik'
go
SELECT name FROM sys.databases
go
REVERT
go
DROP DATABASE eriks
go
DROP LOGIN erik
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||As Erland said the user must be db_Owner or it will not work. I tested this
in SQL 2005 SP2. Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005|||Leila
This should work if the user is the owner of the database.
? Create a new SQL login "login1"
? Create a user named ?login1? in master database
? Grant CREATE DATABASE to login1
? While impersonating login1, create a database called ?dbteste?
? Revoke CREATE DATABASE permission from login1
? Revoke VIEW ANY DATABASE permission from PUBLIC
? Register this server as login1
? From the ?login1? session, expand database tree. Now, you should see
master, tempdb, dbteste
? Grant VIEW ANY DATABASE to PUBLIC
? From the ?login1? session, you should see all the databases
"Leila" <Leilas@.hotpop.com> wrote in message
news:uKzGwhBwHHA.4184@.TK2MSFTNGP06.phx.gbl...
> Hi,
> For a web hosting company, I need to grant access to users the way that
> when they connect to SQL Server using SSMS, they must only be able to see
> their own databases.
> I tried to revoke VIEW ANY DATABASE that caused list of databases
> disappear from SSMS, but how can I turn back particular database for a
> user?
> Thanks in advance,
> Leila
>|||Thanks everybody!
It seems that it doesn't work for membership of the user in db_owner
database role. The login must be the owner of database, is that true?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eJ$Q76SwHHA.1524@.TK2MSFTNGP06.phx.gbl...
> Leila
> This should work if the user is the owner of the database.
> ? Create a new SQL login "login1"
> ? Create a user named ?login1? in master database
> ? Grant CREATE DATABASE to login1
> ? While impersonating login1, create a database called ?dbteste?
> ? Revoke CREATE DATABASE permission from login1
> ? Revoke VIEW ANY DATABASE permission from PUBLIC
> ? Register this server as login1
> ? From the ?login1? session, expand database tree. Now, you should see
> master, tempdb, dbteste
> ? Grant VIEW ANY DATABASE to PUBLIC
> ? From the ?login1? session, you should see all the databases
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uKzGwhBwHHA.4184@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> For a web hosting company, I need to grant access to users the way that
>> when they connect to SQL Server using SSMS, they must only be able to see
>> their own databases.
>> I tried to revoke VIEW ANY DATABASE that caused list of databases
>> disappear from SSMS, but how can I turn back particular database for a
>> user?
>> Thanks in advance,
>> Leila
>|||Leila (Leilas@.hotpop.com) writes:
> It seems that it doesn't work for membership of the user in db_owner
> database role. The login must be the owner of database, is that true?
Yes, that's the way it is. To determine whether a user is entitled to
see the definition of a certain database, SQL Server looks at server-
level information only. Whether a user may be part of the db_owner role
in a certain database, would reqiure SQL Server to peek into that database,
and this could be very costly if there are many databases on the server,
and particularly if they are set to autoclose.
I have a suggestion on Connect about this:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=273830
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Friday, March 9, 2012
Limiting access to SQL Server 2000 for our junior developers
Hi All
I've been given a task to lockdown access to our database server to our
6 junior developers. We currently have about 25 databases on our SQL
Server. I'm trying to do the following the easiest way:
1. Create six separate logins
2. For all 25 db's only allow selects, inserts and updates for them to
all tables.
3. They must be able to create tables but not drop or truncate
anything.
4. Of course no access to the master db
5. Lock out all dangerous sp's like xp_cmdshell
I'm new to SQL permissions. What would be the easiest way to doing
this without manually adding all the permissions table level for each
login?
Thanks in advance for any help.
BillCreate a database role
grant the role data_reader permission
grant the role data_writer permission
grant the role the CREATE TABLE permission
deny the role DELETE Permission on any object
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
<bcrenshaw99@.yahoo.com> wrote in message
news:1123541934.751731.34430@.o13g2000cwo.googlegroups.com...
> Hi All
> I've been given a task to lockdown access to our database server to our
> 6 junior developers. We currently have about 25 databases on our SQL
> Server. I'm trying to do the following the easiest way:
> 1. Create six separate logins
> 2. For all 25 db's only allow selects, inserts and updates for them to
> all tables.
> 3. They must be able to create tables but not drop or truncate
> anything.
> 4. Of course no access to the master db
> 5. Lock out all dangerous sp's like xp_cmdshell
> I'm new to SQL permissions. What would be the easiest way to doing
> this without manually adding all the permissions table level for each
> login?
> Thanks in advance for any help.
> Bill
>|||Dandy
> grant the role data_writer permission
If you add him to the db_denydatawriter hi cannot be able to delete
anything
Also , don't forget to execute EXEC sp_defaultdb 'LoginName', 'database'
as well as EXEC sp_grantdbaccess 'test'
"Dandy Weyn [Dandyman]" <dandy@.dandyman.net> wrote in message
news:eAwwwNKnFHA.1948@.TK2MSFTNGP12.phx.gbl...
> Create a database role
> grant the role data_reader permission
> grant the role data_writer permission
> grant the role the CREATE TABLE permission
> deny the role DELETE Permission on any object
>
> --
> Dandy Weyn
> [MCSE-MCSA-MCDBA-MCDST-MCT]
> http://www.dandyman.net
> Check my SQL Server Resource Pages at http://www.dandyman.net/sql
> <bcrenshaw99@.yahoo.com> wrote in message
> news:1123541934.751731.34430@.o13g2000cwo.googlegroups.com...
>|||Hey Dandy
Thanks for the quick response :-)
I would assume that because database roles are created per database, I
would have do this on all 25 db's?
Dandy Weyn [Dandyman] wrote:[vbcol=seagreen]
> Create a database role
> grant the role data_reader permission
> grant the role data_writer permission
> grant the role the CREATE TABLE permission
> deny the role DELETE Permission on any object
>
> --
> Dandy Weyn
> [MCSE-MCSA-MCDBA-MCDST-MCT]
> http://www.dandyman.net
> Check my SQL Server Resource Pages at http://www.dandyman.net/sql
> <bcrenshaw99@.yahoo.com> wrote in message
> news:1123541934.751731.34430@.o13g2000cwo.googlegroups.com...|||Exactly,
However if you would add the role to the deny_data_writer as Uri specified,
the user would also not be able to insert/update which was needed in your
case.
You need to do this on every database yes.
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
<bcrenshaw99@.yahoo.com> wrote in message
news:1123565986.701157.304890@.g49g2000cwa.googlegroups.com...
> Hey Dandy
> Thanks for the quick response :-)
> I would assume that because database roles are created per database, I
> would have do this on all 25 db's?
>
>
> Dandy Weyn [Dandyman] wrote:
>|||Right, my mistake
"Dandy Weyn [Dandyman]" <dandy@.dandyman.net> wrote in message
news:OUxktvKnFHA.1468@.TK2MSFTNGP12.phx.gbl...
> Exactly,
> However if you would add the role to the deny_data_writer as Uri
> specified, the user would also not be able to insert/update which was
> needed in your case.
> You need to do this on every database yes.
>
> --
> Dandy Weyn
> [MCSE-MCSA-MCDBA-MCDST-MCT]
> http://www.dandyman.net
> Check my SQL Server Resource Pages at http://www.dandyman.net/sql
> <bcrenshaw99@.yahoo.com> wrote in message
> news:1123565986.701157.304890@.g49g2000cwa.googlegroups.com...
>
I've been given a task to lockdown access to our database server to our
6 junior developers. We currently have about 25 databases on our SQL
Server. I'm trying to do the following the easiest way:
1. Create six separate logins
2. For all 25 db's only allow selects, inserts and updates for them to
all tables.
3. They must be able to create tables but not drop or truncate
anything.
4. Of course no access to the master db
5. Lock out all dangerous sp's like xp_cmdshell
I'm new to SQL permissions. What would be the easiest way to doing
this without manually adding all the permissions table level for each
login?
Thanks in advance for any help.
BillCreate a database role
grant the role data_reader permission
grant the role data_writer permission
grant the role the CREATE TABLE permission
deny the role DELETE Permission on any object
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
<bcrenshaw99@.yahoo.com> wrote in message
news:1123541934.751731.34430@.o13g2000cwo.googlegroups.com...
> Hi All
> I've been given a task to lockdown access to our database server to our
> 6 junior developers. We currently have about 25 databases on our SQL
> Server. I'm trying to do the following the easiest way:
> 1. Create six separate logins
> 2. For all 25 db's only allow selects, inserts and updates for them to
> all tables.
> 3. They must be able to create tables but not drop or truncate
> anything.
> 4. Of course no access to the master db
> 5. Lock out all dangerous sp's like xp_cmdshell
> I'm new to SQL permissions. What would be the easiest way to doing
> this without manually adding all the permissions table level for each
> login?
> Thanks in advance for any help.
> Bill
>|||Dandy
> grant the role data_writer permission
If you add him to the db_denydatawriter hi cannot be able to delete
anything
Also , don't forget to execute EXEC sp_defaultdb 'LoginName', 'database'
as well as EXEC sp_grantdbaccess 'test'
"Dandy Weyn [Dandyman]" <dandy@.dandyman.net> wrote in message
news:eAwwwNKnFHA.1948@.TK2MSFTNGP12.phx.gbl...
> Create a database role
> grant the role data_reader permission
> grant the role data_writer permission
> grant the role the CREATE TABLE permission
> deny the role DELETE Permission on any object
>
> --
> Dandy Weyn
> [MCSE-MCSA-MCDBA-MCDST-MCT]
> http://www.dandyman.net
> Check my SQL Server Resource Pages at http://www.dandyman.net/sql
> <bcrenshaw99@.yahoo.com> wrote in message
> news:1123541934.751731.34430@.o13g2000cwo.googlegroups.com...
>|||Hey Dandy
Thanks for the quick response :-)
I would assume that because database roles are created per database, I
would have do this on all 25 db's?
Dandy Weyn [Dandyman] wrote:[vbcol=seagreen]
> Create a database role
> grant the role data_reader permission
> grant the role data_writer permission
> grant the role the CREATE TABLE permission
> deny the role DELETE Permission on any object
>
> --
> Dandy Weyn
> [MCSE-MCSA-MCDBA-MCDST-MCT]
> http://www.dandyman.net
> Check my SQL Server Resource Pages at http://www.dandyman.net/sql
> <bcrenshaw99@.yahoo.com> wrote in message
> news:1123541934.751731.34430@.o13g2000cwo.googlegroups.com...|||Exactly,
However if you would add the role to the deny_data_writer as Uri specified,
the user would also not be able to insert/update which was needed in your
case.
You need to do this on every database yes.
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
<bcrenshaw99@.yahoo.com> wrote in message
news:1123565986.701157.304890@.g49g2000cwa.googlegroups.com...
> Hey Dandy
> Thanks for the quick response :-)
> I would assume that because database roles are created per database, I
> would have do this on all 25 db's?
>
>
> Dandy Weyn [Dandyman] wrote:
>|||Right, my mistake
"Dandy Weyn [Dandyman]" <dandy@.dandyman.net> wrote in message
news:OUxktvKnFHA.1468@.TK2MSFTNGP12.phx.gbl...
> Exactly,
> However if you would add the role to the deny_data_writer as Uri
> specified, the user would also not be able to insert/update which was
> needed in your case.
> You need to do this on every database yes.
>
> --
> Dandy Weyn
> [MCSE-MCSA-MCDBA-MCDST-MCT]
> http://www.dandyman.net
> Check my SQL Server Resource Pages at http://www.dandyman.net/sql
> <bcrenshaw99@.yahoo.com> wrote in message
> news:1123565986.701157.304890@.g49g2000cwa.googlegroups.com...
>
Limiting access to certain DB's
I need to install EM and QA on my users machines, is there a way to limit their access to only certain databases in EM and QA? I only want them to see say database X in both EM and QA, and also limit what they can do it in QA(like Select, run make table/
update queries)? What would be the best way to manage this? How can I force a user to logon with a cetain Login account in EM and QA? Thanks for your help!
Easiest is to use Windows logins in SQL Server and then work with the regular security system inside SQL
Server (logins, users, roles and permissions).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"mikeb" <anonymous@.discussions.microsoft.com> wrote in message
news:F2CE354A-60B3-4914-B572-98F3DDE9D842@.microsoft.com...
> I need to install EM and QA on my users machines, is there a way to limit their access to only certain
databases in EM and QA? I only want them to see say database X in both EM and QA, and also limit what they
can do it in QA(like Select, run make table/update queries)? What would be the best way to manage this? How
can I force a user to logon with a cetain Login account in EM and QA? Thanks for your help!
|||mikeb wrote:
> I need to install EM and QA on my users machines, is there a way to
> limit their access to only certain databases in EM and QA? I only
> want them to see say database X in both EM and QA, and also limit
> what they can do it in QA(like Select, run make table/update
> queries)? What would be the best way to manage this? How can I
> force a user to logon with a cetain Login account in EM and QA?
> Thanks for your help!
What Tibor said is the best answer. In addition, you may want to have a look
at our OmniView product. It is a query tool designed for users who aren't
the DBA type. It integrates their login permissions, so data editing
controls are disabled if they don't have the appropriate Update, Insert, or
Delete permissions. If they don't have any access to particular databases,
or tables, they aren't displayed at all in the various lists.
It does not perform the schema modification functions of EM, but it is a
viable query tool to serve the purpose of QA.
Steve Troxell
Krell Software - Database Tools for MS SQL Server
http://www.krell-software.com
update queries)? What would be the best way to manage this? How can I force a user to logon with a cetain Login account in EM and QA? Thanks for your help!
Easiest is to use Windows logins in SQL Server and then work with the regular security system inside SQL
Server (logins, users, roles and permissions).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"mikeb" <anonymous@.discussions.microsoft.com> wrote in message
news:F2CE354A-60B3-4914-B572-98F3DDE9D842@.microsoft.com...
> I need to install EM and QA on my users machines, is there a way to limit their access to only certain
databases in EM and QA? I only want them to see say database X in both EM and QA, and also limit what they
can do it in QA(like Select, run make table/update queries)? What would be the best way to manage this? How
can I force a user to logon with a cetain Login account in EM and QA? Thanks for your help!
|||mikeb wrote:
> I need to install EM and QA on my users machines, is there a way to
> limit their access to only certain databases in EM and QA? I only
> want them to see say database X in both EM and QA, and also limit
> what they can do it in QA(like Select, run make table/update
> queries)? What would be the best way to manage this? How can I
> force a user to logon with a cetain Login account in EM and QA?
> Thanks for your help!
What Tibor said is the best answer. In addition, you may want to have a look
at our OmniView product. It is a query tool designed for users who aren't
the DBA type. It integrates their login permissions, so data editing
controls are disabled if they don't have the appropriate Update, Insert, or
Delete permissions. If they don't have any access to particular databases,
or tables, they aren't displayed at all in the various lists.
It does not perform the schema modification functions of EM, but it is a
viable query tool to serve the purpose of QA.
Steve Troxell
Krell Software - Database Tools for MS SQL Server
http://www.krell-software.com
Wednesday, March 7, 2012
limited # of db's?
Running SQL 2000 Enterprise Edition. Is there a limit to the number of
databases one installaion can support, other than disk space/hardware
limitations?32,767 per instance. In BOL under 'maximum capacity specifications' there is
a list of this type of data.
Rgds,
Paul Ibison (SQL Server MVP)
databases one installaion can support, other than disk space/hardware
limitations?32,767 per instance. In BOL under 'maximum capacity specifications' there is
a list of this type of data.
Rgds,
Paul Ibison (SQL Server MVP)
Friday, February 24, 2012
Limit to number of databases in Analysis Services 2005
We are trying to run a load test on analysis services 2005. We were
going to start with 30 different databases out there and see what the
processing loads were. Analysis Services is stopping us at 25
databases and telling us that there is no more room. We have 260 GB
free on the server. Has anyone run into this before? Is there a limit
to the number of databases you can have? Does it vary by SQL edition?http://msdn2.microsoft.com/en-us/library/ms365363.aspx
<rbergstrom@.spectrumhr.com> wrote in message
news:1154470182.648978.116860@.75g2000cwc.googlegroups.com...
> We are trying to run a load test on analysis services 2005. We were
> going to start with 30 different databases out there and see what the
> processing loads were. Analysis Services is stopping us at 25
> databases and telling us that there is no more room. We have 260 GB
> free on the server. Has anyone run into this before? Is there a limit
> to the number of databases you can have? Does it vary by SQL edition?
>|||Thanks for that link. Now I will ask a dumb question. in the page on
that link it shows Maximum sizes/numbers. The value for Databases in
an instance is 2^31-1 = 2,147,483,647. Is that a total size in bytes?
The number of databases? I don't understand what that is supposed to
represent. Thanks for your help.
Immy wrote:
> http://msdn2.microsoft.com/en-us/library/ms365363.aspx
> <rbergstrom@.spectrumhr.com> wrote in message
> news:1154470182.648978.116860@.75g2000cwc.googlegroups.com...
> > We are trying to run a load test on analysis services 2005. We were
> > going to start with 30 different databases out there and see what the
> > processing loads were. Analysis Services is stopping us at 25
> > databases and telling us that there is no more room. We have 260 GB
> > free on the server. Has anyone run into this before? Is there a limit
> > to the number of databases you can have? Does it vary by SQL edition?
> >
going to start with 30 different databases out there and see what the
processing loads were. Analysis Services is stopping us at 25
databases and telling us that there is no more room. We have 260 GB
free on the server. Has anyone run into this before? Is there a limit
to the number of databases you can have? Does it vary by SQL edition?http://msdn2.microsoft.com/en-us/library/ms365363.aspx
<rbergstrom@.spectrumhr.com> wrote in message
news:1154470182.648978.116860@.75g2000cwc.googlegroups.com...
> We are trying to run a load test on analysis services 2005. We were
> going to start with 30 different databases out there and see what the
> processing loads were. Analysis Services is stopping us at 25
> databases and telling us that there is no more room. We have 260 GB
> free on the server. Has anyone run into this before? Is there a limit
> to the number of databases you can have? Does it vary by SQL edition?
>|||Thanks for that link. Now I will ask a dumb question. in the page on
that link it shows Maximum sizes/numbers. The value for Databases in
an instance is 2^31-1 = 2,147,483,647. Is that a total size in bytes?
The number of databases? I don't understand what that is supposed to
represent. Thanks for your help.
Immy wrote:
> http://msdn2.microsoft.com/en-us/library/ms365363.aspx
> <rbergstrom@.spectrumhr.com> wrote in message
> news:1154470182.648978.116860@.75g2000cwc.googlegroups.com...
> > We are trying to run a load test on analysis services 2005. We were
> > going to start with 30 different databases out there and see what the
> > processing loads were. Analysis Services is stopping us at 25
> > databases and telling us that there is no more room. We have 260 GB
> > free on the server. Has anyone run into this before? Is there a limit
> > to the number of databases you can have? Does it vary by SQL edition?
> >
Limit to number of databases in Analysis Services 2005
We are trying to run a load test on analysis services 2005. We were
going to start with 30 different databases out there and see what the
processing loads were. Analysis Services is stopping us at 25
databases and telling us that there is no more room. We have 260 GB
free on the server. Has anyone run into this before? Is there a limit
to the number of databases you can have? Does it vary by SQL edition?http://msdn2.microsoft.com/en-us/library/ms365363.aspx
<rbergstrom@.spectrumhr.com> wrote in message
news:1154470182.648978.116860@.75g2000cwc.googlegroups.com...
> We are trying to run a load test on analysis services 2005. We were
> going to start with 30 different databases out there and see what the
> processing loads were. Analysis Services is stopping us at 25
> databases and telling us that there is no more room. We have 260 GB
> free on the server. Has anyone run into this before? Is there a limit
> to the number of databases you can have? Does it vary by SQL edition?
>|||Thanks for that link. Now I will ask a dumb question. in the page on
that link it shows Maximum sizes/numbers. The value for Databases in
an instance is 2^31-1 = 2,147,483,647. Is that a total size in bytes?
The number of databases? I don't understand what that is supposed to
represent. Thanks for your help.
Immy wrote:[vbcol=seagreen]
> http://msdn2.microsoft.com/en-us/library/ms365363.aspx
> <rbergstrom@.spectrumhr.com> wrote in message
> news:1154470182.648978.116860@.75g2000cwc.googlegroups.com...
going to start with 30 different databases out there and see what the
processing loads were. Analysis Services is stopping us at 25
databases and telling us that there is no more room. We have 260 GB
free on the server. Has anyone run into this before? Is there a limit
to the number of databases you can have? Does it vary by SQL edition?http://msdn2.microsoft.com/en-us/library/ms365363.aspx
<rbergstrom@.spectrumhr.com> wrote in message
news:1154470182.648978.116860@.75g2000cwc.googlegroups.com...
> We are trying to run a load test on analysis services 2005. We were
> going to start with 30 different databases out there and see what the
> processing loads were. Analysis Services is stopping us at 25
> databases and telling us that there is no more room. We have 260 GB
> free on the server. Has anyone run into this before? Is there a limit
> to the number of databases you can have? Does it vary by SQL edition?
>|||Thanks for that link. Now I will ask a dumb question. in the page on
that link it shows Maximum sizes/numbers. The value for Databases in
an instance is 2^31-1 = 2,147,483,647. Is that a total size in bytes?
The number of databases? I don't understand what that is supposed to
represent. Thanks for your help.
Immy wrote:[vbcol=seagreen]
> http://msdn2.microsoft.com/en-us/library/ms365363.aspx
> <rbergstrom@.spectrumhr.com> wrote in message
> news:1154470182.648978.116860@.75g2000cwc.googlegroups.com...
Monday, February 20, 2012
Limit on number of databases?
Hi,
What is the limit on the number of databases for SSAS 2005 Standard Edition?
Is there a performance hit on the server having lots of databases? Or is it better to have a single database and create multiple cubes to serve your purpose? Note that the data stored in each cube will not be related to any of the other cubes in the database...
Thanks
mmmman wrote:
What is the limit on the number of databases for SSAS 2005 Standard Edition?
2,147,483,647 (see http://msdn2.microsoft.com/en-us/library/ms365363.aspx)
Is there a performance hit on the server having lots of databases? Or is it better to have a single database and create multiple cubes to serve your purpose? Note that the data stored in each cube will not be related to any of the other cubes in the database...
It probably depends on what you mean by "lots". The more databases you have the longer it will take to load the meta data relating to them. So with a lot of databases it will take the server longer to startup and it will obviously take longer to get a list of the available databases. So I would maybe lean towards saying that less databases is better, but this is not a choice I usually have to make, usually there is a logical split. If you are using the same, or mostly the same dimensions, then putting everything in the one database would help with cache re-use. If everything, dimensions and facts are independant, then they probably belong in separate databases.
You would also need to consider backup and restore. If everything is in one database then it must all be backed up and restored together.
|||Great, thanks Darren...
Subscribe to:
Posts (Atom)