hide system tables: databases

Showing posts with label databases. Show all posts

Wednesday, March 21, 2012

How to include more databases in DB Maintenance Plan ?

Hi,
I have created a DB maintenance plan to backup a production database daily.
There is a request to include 2 more databases to be included in the
maintenance plan. I have checked the maintenance plan but I am not able to
work out how to include 2 more databases.
Is it possible to give me some advice ?
ThanksPeter
Right Click on MP and then Properties . Add more user databases. Is that
what you mean?
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:OZusmMoIHHA.2456@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I have created a DB maintenance plan to backup a production database
> daily.
> There is a request to include 2 more databases to be included in the
> maintenance plan. I have checked the maintenance plan but I am not able
> to work out how to include 2 more databases.
> Is it possible to give me some advice ?
> Thanks
>|||Yes.
Peter
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O8m1RToIHHA.780@.TK2MSFTNGP03.phx.gbl...
> Peter
> Right Click on MP and then Properties . Add more user databases. Is that
> what you mean?
>
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:OZusmMoIHHA.2456@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> I have created a DB maintenance plan to backup a production database
>> daily.
>> There is a request to include 2 more databases to be included in the
>> maintenance plan. I have checked the maintenance plan but I am not able
>> to work out how to include 2 more databases.
>> Is it possible to give me some advice ?
>> Thanks
>

How to include more databases in DB Maintenance Plan ?

How to include more databases in DB Mainteance Plan for SQL Server 2005

Hi,
I have created a DB maintenance plan by using wizard to backup a production
database daily.
There is a request to include 2 more databases to be included in the
maintenance plan. I select the maintenance plan and press "Modify" but I am
not able to work out how to include 2 more databases. Besides, it appears
that both transaction log & database backup are not deleted since the plan
is executed, is there something wrong ?
Is it possible to give me some advice ?
ThanksPeter
You asked the question yesterday and I gave you the answer. Don't you
rememner that?
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:u01Fh30IHHA.4712@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have created a DB maintenance plan by using wizard to backup a
> production database daily.
> There is a request to include 2 more databases to be included in the
> maintenance plan. I select the maintenance plan and press "Modify" but I
> am not able to work out how to include 2 more databases. Besides, it
> appears that both transaction log & database backup are not deleted since
> the plan is executed, is there something wrong ?
> Is it possible to give me some advice ?
> Thanks
>|||> There is a request to include 2 more databases to be included in the maintenance plan. I select
> the maintenance plan and press "Modify" but I am not able to work out how to include 2 more
> databases.
You need to add the databases to the backup task inside the plan. Right-lock the backup task, select
"Edit..." and in the "Databases:" drop.down you select the databases you want to include.
> Besides, it appears that both transaction log & database backup are not deleted since the plan is
> executed, is there something wrong ?
Add a "Maintenance Cleanup Task" to the plan.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:u01Fh30IHHA.4712@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have created a DB maintenance plan by using wizard to backup a production database daily.
> There is a request to include 2 more databases to be included in the maintenance plan. I select
> the maintenance plan and press "Modify" but I am not able to work out how to include 2 more
> databases. Besides, it appears that both transaction log & database backup are not deleted since
> the plan is executed, is there something wrong ?
> Is it possible to give me some advice ?
> Thanks
>|||Dear Uri,
I forget to mention that it is SQL Server 2005 yesterday. In this way, your
advice is applicable for SQL Server 2000.
Thanks
Peter
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uEfBDB1IHHA.4712@.TK2MSFTNGP04.phx.gbl...
> Peter
> You asked the question yesterday and I gave you the answer. Don't you
> rememner that?
>
>
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:u01Fh30IHHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I have created a DB maintenance plan by using wizard to backup a
>> production database daily.
>> There is a request to include 2 more databases to be included in the
>> maintenance plan. I select the maintenance plan and press "Modify" but I
>> am not able to work out how to include 2 more databases. Besides, it
>> appears that both transaction log & database backup are not deleted since
>> the plan is executed, is there something wrong ?
>> Is it possible to give me some advice ?
>> Thanks
>>
>|||Dear Tibor and Uri,
Thank you for your advice.
I find that the reason why I am not able to add more databases in the task
is because I use SA in my workstation while Windows Authentication is used
at the server side. In this way, I change the connection from Windows
Authentication to SQL Authentication for both local and target servers and I
am able to do it on my workstation. I have changed the ownership of the
jobs to SA (Instead of Administrator).
I would like to seek your advice
1) Is it possible to change the ownership of the "Database Maintenance Plan"
from Administrator to SA ?
2) Is it necessary to add the "Cleanup Task" for the daily transaction log
maintenance plan so that old transaction log backup will be deleted ?
3) Which step should be performed first - Cleanup Task or Backup Task ?
Should the constraint be success or finish ?
4) Is it necessary to add another task to delete old reports in the "Weekly
Maintenance Plan" so that the logging will be deleted ?
Thanks
Peter
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O4yE4B1IHHA.2236@.TK2MSFTNGP02.phx.gbl...
>> There is a request to include 2 more databases to be included in the
>> maintenance plan. I select the maintenance plan and press "Modify" but I
>> am not able to work out how to include 2 more databases.
> You need to add the databases to the backup task inside the plan.
> Right-lock the backup task, select "Edit..." and in the "Databases:"
> drop.down you select the databases you want to include.
>
>> Besides, it appears that both transaction log & database backup are not
>> deleted since the plan is executed, is there something wrong ?
> Add a "Maintenance Cleanup Task" to the plan.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:u01Fh30IHHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I have created a DB maintenance plan by using wizard to backup a
>> production database daily.
>> There is a request to include 2 more databases to be included in the
>> maintenance plan. I select the maintenance plan and press "Modify" but I
>> am not able to work out how to include 2 more databases. Besides, it
>> appears that both transaction log & database backup are not deleted since
>> the plan is executed, is there something wrong ?
>> Is it possible to give me some advice ?
>> Thanks
>>
>|||> 1) Is it possible to change the ownership of the "Database Maintenance Plan" from Administrator to
> SA ?
I would guess that you would change the owner of the job. To the best of my knowledge, a maint plan
doesn't have an owner, the job does. Not sure, though.
> 2) Is it necessary to add the "Cleanup Task" for the daily transaction log maintenance plan so
> that old transaction log backup will be deleted ?
Yes, if you want the old backup files to be removed and if you don't remove them some other way.
> 3) Which step should be performed first - Cleanup Task or Backup Task ? Should the constraint be
> success or finish ?
This is really your decision. I prefer to do the backup first, and if it fails I don't remove old
backups.
> 4) Is it necessary to add another task to delete old reports in the "Weekly Maintenance Plan" so
> that the logging will be deleted ?
Yes, if you want the old report files to be removed and if you don't remove them some other way.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:%23gNfQpBJHHA.4000@.TK2MSFTNGP06.phx.gbl...
> Dear Tibor and Uri,
> Thank you for your advice.
> I find that the reason why I am not able to add more databases in the task is because I use SA in
> my workstation while Windows Authentication is used at the server side. In this way, I change the
> connection from Windows Authentication to SQL Authentication for both local and target servers and
> I am able to do it on my workstation. I have changed the ownership of the jobs to SA (Instead of
> Administrator).
> I would like to seek your advice
> 1) Is it possible to change the ownership of the "Database Maintenance Plan" from Administrator to
> SA ?
> 2) Is it necessary to add the "Cleanup Task" for the daily transaction log maintenance plan so
> that old transaction log backup will be deleted ?
> 3) Which step should be performed first - Cleanup Task or Backup Task ? Should the constraint be
> success or finish ?
> 4) Is it necessary to add another task to delete old reports in the "Weekly Maintenance Plan" so
> that the logging will be deleted ?
> Thanks
> Peter
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:O4yE4B1IHHA.2236@.TK2MSFTNGP02.phx.gbl...
>> There is a request to include 2 more databases to be included in the maintenance plan. I select
>> the maintenance plan and press "Modify" but I am not able to work out how to include 2 more
>> databases.
>> You need to add the databases to the backup task inside the plan. Right-lock the backup task,
>> select "Edit..." and in the "Databases:" drop.down you select the databases you want to include.
>>
>> Besides, it appears that both transaction log & database backup are not deleted since the plan
>> is executed, is there something wrong ?
>> Add a "Maintenance Cleanup Task" to the plan.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Peter" <Peter@.discussions.microsoft.com> wrote in message
>> news:u01Fh30IHHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I have created a DB maintenance plan by using wizard to backup a production database daily.
>> There is a request to include 2 more databases to be included in the maintenance plan. I select
>> the maintenance plan and press "Modify" but I am not able to work out how to include 2 more
>> databases. Besides, it appears that both transaction log & database backup are not deleted
>> since the plan is executed, is there something wrong ?
>> Is it possible to give me some advice ?
>> Thanks
>>
>|||Dear Tibor,
Thank you for your advice.
Peter
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ejNfWICJHHA.3424@.TK2MSFTNGP02.phx.gbl...
>> 1) Is it possible to change the ownership of the "Database Maintenance
>> Plan" from Administrator to SA ?
> I would guess that you would change the owner of the job. To the best of
> my knowledge, a maint plan doesn't have an owner, the job does. Not sure,
> though.
>
>> 2) Is it necessary to add the "Cleanup Task" for the daily transaction
>> log maintenance plan so that old transaction log backup will be deleted ?
> Yes, if you want the old backup files to be removed and if you don't
> remove them some other way.
>
>> 3) Which step should be performed first - Cleanup Task or Backup Task ?
>> Should the constraint be success or finish ?
> This is really your decision. I prefer to do the backup first, and if it
> fails I don't remove old backups.
>
>> 4) Is it necessary to add another task to delete old reports in the
>> "Weekly Maintenance Plan" so that the logging will be deleted ?
> Yes, if you want the old report files to be removed and if you don't
> remove them some other way.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:%23gNfQpBJHHA.4000@.TK2MSFTNGP06.phx.gbl...
>> Dear Tibor and Uri,
>> Thank you for your advice.
>> I find that the reason why I am not able to add more databases in the
>> task is because I use SA in my workstation while Windows Authentication
>> is used at the server side. In this way, I change the connection from
>> Windows Authentication to SQL Authentication for both local and target
>> servers and I am able to do it on my workstation. I have changed the
>> ownership of the jobs to SA (Instead of Administrator).
>> I would like to seek your advice
>> 1) Is it possible to change the ownership of the "Database Maintenance
>> Plan" from Administrator to SA ?
>> 2) Is it necessary to add the "Cleanup Task" for the daily transaction
>> log maintenance plan so that old transaction log backup will be deleted ?
>> 3) Which step should be performed first - Cleanup Task or Backup Task ?
>> Should the constraint be success or finish ?
>> 4) Is it necessary to add another task to delete old reports in the
>> "Weekly Maintenance Plan" so that the logging will be deleted ?
>> Thanks
>> Peter
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:O4yE4B1IHHA.2236@.TK2MSFTNGP02.phx.gbl...
>> There is a request to include 2 more databases to be included in the
>> maintenance plan. I select the maintenance plan and press "Modify" but
>> I am not able to work out how to include 2 more databases.
>> You need to add the databases to the backup task inside the plan.
>> Right-lock the backup task, select "Edit..." and in the "Databases:"
>> drop.down you select the databases you want to include.
>>
>> Besides, it appears that both transaction log & database backup are not
>> deleted since the plan is executed, is there something wrong ?
>> Add a "Maintenance Cleanup Task" to the plan.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Peter" <Peter@.discussions.microsoft.com> wrote in message
>> news:u01Fh30IHHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I have created a DB maintenance plan by using wizard to backup a
>> production database daily.
>> There is a request to include 2 more databases to be included in the
>> maintenance plan. I select the maintenance plan and press "Modify" but
>> I am not able to work out how to include 2 more databases. Besides, it
>> appears that both transaction log & database backup are not deleted
>> since the plan is executed, is there something wrong ?
>> Is it possible to give me some advice ?
>> Thanks
>>
>>
>sql

Monday, March 19, 2012

How to import/export databases from remote server to local SQL 2005 Express

I've successfully installed SQL2005 express edition and SQL Server Management Studio Express. All seems well except there does not appear to be any way to import databases from other servers to my local server. The remote servers are running SQL Server 7.

In the Management Studio Express I can connect to the remote databases and work with them, but I can neither import them into my local machine, nor can I export from remote to local (the option doesn't seem to exist). By contrast, using the Enterprise Manager in SQL 7 there has always been the option to do this under the All Tasks menu.

If this cannot be done, is there a way to backup a remote database to my local disk and then restore it into my local DB?

Is this by design? Can anyone help with this?

Thanks in advance.

First, you can always detach the databases on the old server using sp_detachdb, move the files to the new server, and attatch them using sp_attachdb. Check BOL for detailed documentation on these stored procedures.

Second, I've moved this thread to the tools forum where experts can tell you if there's a way to do this through Management Studio Express (I'm guessing not).

Paul

Friday, February 24, 2012

How to implement Application Roles for an application?

I want to know how to implement Application Roles for an application which
uses mutliple databases. Based on what I have read, Application Roles are
tied to one database only and accessing other databases requires adding gues
t
user account to other databases. For example, if an application is using on
e
database to contain access rights data to its functions and another database
for actual transaction data, how can I implement Application Role?
Thanks.Your understanding is correct; once an application role is activated, other
databases can be accessed only via the guest user security context.
If you don't want to grant permissions to guest or public in the other
databases, consider creating referencing views or procs in your application
role database and enabling cross-database chaining in the databases
involved. As long as the objects involved have the same owner, permissions
are not needed on indirectly referenced objects. Note that the databases
also need to be owned by the same login in order to maintain an unbroken
ownership chain for dbo-owned objects.
You should enable cross-database chaining only if you fully understand the
security implications. See the Books Online
<instsql.chm::/in_runsetup_1cj5.htm> for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:36AC8304-5158-4335-B1C4-B503F51E3F43@.microsoft.com...
>I want to know how to implement Application Roles for an application which
> uses mutliple databases. Based on what I have read, Application Roles are
> tied to one database only and accessing other databases requires adding
> guest
> user account to other databases. For example, if an application is using
> one
> database to contain access rights data to its functions and another
> database
> for actual transaction data, how can I implement Application Role?
>
> Thanks.|||Hi Dan,
Can you elaborate little bit more about referencing views and procedure?
My understanding of Application role:
1. It is connection-based (session-based).
2. It needs to be activated by using sp_setapprole.
3. By default, application role has no permission on the databases. So,
permissions need to be assigned to the role.
I'm trying to develop an application which can connect to SQL Server using
either SQL Authentication or Windows Authentication through ODBC. The
administrator(s) of the application can configure whether the databases
created by the application can be accessed by other applications (this is wh
y
I'm looking at application role). The application is using multiple
databases per connection/session.
Thanks,
Peter
"Dan Guzman" wrote:

> Your understanding is correct; once an application role is activated, othe
r
> databases can be accessed only via the guest user security context.
> If you don't want to grant permissions to guest or public in the other
> databases, consider creating referencing views or procs in your applicatio
n
> role database and enabling cross-database chaining in the databases
> involved. As long as the objects involved have the same owner, permission
s
> are not needed on indirectly referenced objects. Note that the databases
> also need to be owned by the same login in order to maintain an unbroken
> ownership chain for dbo-owned objects.
> You should enable cross-database chaining only if you fully understand the
> security implications. See the Books Online
> <instsql.chm::/in_runsetup_1cj5.htm> for more information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:36AC8304-5158-4335-B1C4-B503F51E3F43@.microsoft.com...
>
>|||> Can you elaborate little bit more about referencing views and procedure?
You can create views and/or procs in your application role database that
reference those objects in other databases needed by your application. For
example:
USE MyAppRoleDatabase
GO
CREATE VIEW dbo.MyView
AS
SELECT SomeData
FROM MyOtherDatabase.dbo.MyTable
GO
GRANT ALL ON dbo.MyView TO MyAppRole
GO
As long as MyAppRoleDatabase and MyOtherDatabase are owned by the same
login, the ownership chain for dbo-owned objects is unbroken. This allows
the application role to access MyTable data via MyView even without
permissions the underlying table. As mentioned earlier in this thread, the
guest user needs to be enabled in MyOtherDatabase in order to establish a
security context for the application role.

> 3. By default, application role has no permission on the databases. So,
> permissions need to be assigned to the role.
This is true but note that permissions are needed only on the objects
*directly* referenced by the role. Permissions are not checked on
indirectly referenced objects as long as the ownership chain is unbroken.

> I'm trying to develop an application which can connect to SQL Server using
> either SQL Authentication or Windows Authentication through ODBC.
Application roles can be used with either authentication method.

> The administrator(s) of the application can configure whether the
> databases
> created by the application can be accessed by other applications (this is
> why
> I'm looking at application role).
Are you saying that you have multiple applications and application roles?
In this case, each application role database would need referencing objects
as described above.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:6362673A-F318-429B-946A-FC66BFA2D490@.microsoft.com...[vbcol=seagreen]
> Hi Dan,
> Can you elaborate little bit more about referencing views and procedure?
> My understanding of Application role:
> 1. It is connection-based (session-based).
> 2. It needs to be activated by using sp_setapprole.
> 3. By default, application role has no permission on the databases. So,
> permissions need to be assigned to the role.
> I'm trying to develop an application which can connect to SQL Server using
> either SQL Authentication or Windows Authentication through ODBC. The
> administrator(s) of the application can configure whether the databases
> created by the application can be accessed by other applications (this is
> why
> I'm looking at application role). The application is using multiple
> databases per connection/session.
>
> Thanks,
> Peter
>
> "Dan Guzman" wrote:
>

How to identify unused databases

Hi Folks,
client company has around 25 sql servers .
Each server has got 5 to 10 databases.
Some of the databases are old.
We don't know whether any applications using old databases or not.
Is there any way we can find the unused databases in past 3 months or past 1
year
using some Lastupdatedatetime or last action on sql server system tables
using T-SQL scripts.
If no application is accessing the database and no actions performed on
that database from past 1 year then we need to delete that databases.
How to identify unused databases?
Any kind of help is greatly appreciated.
Thanks
KumarKumar wrote:
> Hi Folks,
> client company has around 25 sql servers .
> Each server has got 5 to 10 databases.
> Some of the databases are old.
> We don't know whether any applications using old databases or not.
> Is there any way we can find the unused databases in past 3 months or
> past 1 year
> using some Lastupdatedatetime or last action on sql server system
> tables using T-SQL scripts.
> If no application is accessing the database and no actions performed
> on that database from past 1 year then we need to delete that
> databases.
> How to identify unused databases?
> Any kind of help is greatly appreciated.
> Thanks
> Kumar
You can create a trace on the server. Include only the SQL:StmtStarting
and RPC:Starting events. Include only the minimum number of columns:
EventClass, SPID, DatabaseID. You could exclude system databases like
master, msdb, and tempdb.
To cut down the rows a little, you could use SQL:BatchStarting, but if a
batch has "USE" statements and accesses more than one database, the
DatabaseID will only refer to the database that was used at execution
time.
If you let the trace run for a couple of hours (or a day or two), you
should have a pretty good picture of what databases were accessed on
each server.
Best to do this using a server-side trace which you can script from
Profiler using the File - Script Trace menu option and stopping manually
using sp_trace_setstatus.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Sunday, February 19, 2012

How to hide unauthorized databases with SQL 2005?

Hi,
I have configure permission for userA and he can access only one database.
When user estabilish the connection via management studio, though he cannot
access other databases, he can see them. Is it possible to hide other
databases for userA?
Appreciate all your reply.
ShaneVIEW ANY DATABASE is granted to public by default. If you want to remove
this permission from userA:
USE master
DENY VIEW ANY DATABASE TO userA
Although the user still has VIEW ANY DATABASE via public role membership,
the DENY takes precedence.
You could also REVOKE VIEW ANY DATABASE from public and then selectively
grant that permission to users as you see fit.
Hope this helps.
Dan Guzman
SQL Server MVP
"SL Coder" <sl_coder@.hotmail.com> wrote in message
news:e50gEfAaGHA.3704@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have configure permission for userA and he can access only one database.
> When user estabilish the connection via management studio, though he
> cannot access other databases, he can see them. Is it possible to hide
> other databases for userA?
> Appreciate all your reply.
> Shane
>|||Thanks for the reply Dan. But the problem is, this statement applies for all
databases that is not what I want. I need to allow userA to see one databas
e while denying other databases. Is it possible. Have I missed anything?
Shane
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message news:%2
300soIBaGHA.3304@.TK2MSFTNGP04.phx.gbl...
VIEW ANY DATABASE is granted to public by default. If you want to remove
this permission from userA:
USE master
DENY VIEW ANY DATABASE TO userA
Although the user still has VIEW ANY DATABASE via public role membership,
the DENY takes precedence.
You could also REVOKE VIEW ANY DATABASE from public and then selectively
grant that permission to users as you see fit.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"SL Coder" <sl_coder@.hotmail.com> wrote in message
news:e50gEfAaGHA.3704@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have configure permission for userA and he can access only one database.
> When user estabilish the connection via management studio, though he
> cannot access other databases, he can see them. Is it possible to hide
> other databases for userA?
> Appreciate all your reply.
> Shane
>|||SL
Well, this unwanted user must be connected via SSMS (am I right?) and if you
have not added him/her to the database , he/she will see the database's nam
e but cannot access to
"SL Coder" <sl_coder@.hotmail.com> wrote in message news:eV8jH9BaGHA.4116@.TK2
MSFTNGP05.phx.gbl...
Thanks for the reply Dan. But the problem is, this statement applies for all
databases that is not what I want. I need to allow userA to see one databas
e while denying other databases. Is it possible. Have I missed anything?
Shane
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message news:%2
300soIBaGHA.3304@.TK2MSFTNGP04.phx.gbl...
VIEW ANY DATABASE is granted to public by default. If you want to remove
this permission from userA:
USE master
DENY VIEW ANY DATABASE TO userA
Although the user still has VIEW ANY DATABASE via public role membership,
the DENY takes precedence.
You could also REVOKE VIEW ANY DATABASE from public and then selectively
grant that permission to users as you see fit.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"SL Coder" <sl_coder@.hotmail.com> wrote in message
news:e50gEfAaGHA.3704@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have configure permission for userA and he can access only one database.
> When user estabilish the connection via management studio, though he
> cannot access other databases, he can see them. Is it possible to hide
> other databases for userA?
> Appreciate all your reply.
> Shane
>|||After VIEW ANY DATABASE is denied, only master, tempdb, and databases that
the login owns are visible. Other databases that the user can access are
not enumerated but can still be accessed directly by setting the database
context (e.g. USE). Unfortunately, SSMS Object Explorer functionality is
limited to visible databases.
The reason for this behavior is that it is necessary to open each database
on the server to determine whether or not a non-privileged login has
database access. This caused performance issues on servers with a lot
(100's) of databases.
If this feature is important to you, make a suggestion (or vote on the
importance if already submitted) at the product feedback center:
http://lab.msdn.microsoft.com/produ...ck/default.aspx
Hope this helps.
Dan Guzman
SQL Server MVP
"SL Coder" <sl_coder@.hotmail.com> wrote in message
news:eV8jH9BaGHA.4116@.TK2MSFTNGP05.phx.gbl...
Thanks for the reply Dan. But the problem is, this statement applies for all
databases that is not what I want. I need to allow userA to see one database
while denying other databases. Is it possible. Have I missed anything?
Shane
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%2300soIBaGHA.3304@.TK2MSFTNGP04.phx.gbl...
VIEW ANY DATABASE is granted to public by default. If you want to remove
this permission from userA:
USE master
DENY VIEW ANY DATABASE TO userA
Although the user still has VIEW ANY DATABASE via public role membership,
the DENY takes precedence.
You could also REVOKE VIEW ANY DATABASE from public and then selectively
grant that permission to users as you see fit.
Hope this helps.
Dan Guzman
SQL Server MVP
"SL Coder" <sl_coder@.hotmail.com> wrote in message
news:e50gEfAaGHA.3704@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have configure permission for userA and he can access only one
database.
> When user estabilish the connection via management studio, though he
> cannot access other databases, he can see them. Is it possible to hide
> other databases for userA?
> Appreciate all your reply.
> Shane
>

Wednesday, March 21, 2012

How to include more databases in DB Maintenance Plan ?

How to include more databases in DB Maintenance Plan ?

How to include more databases in DB Mainteance Plan for SQL Server 2005

How to include more databases in DB Mainteance Plan for SQL Server 2005

Monday, March 19, 2012

How to import/export databases from remote server to local SQL 2005 Express

Friday, February 24, 2012

How to implement Application Roles for an application?

How to identify unused databases

Sunday, February 19, 2012

How to hide unauthorized databases with SQL 2005?

hide system tables

Blog Archive

About Me