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
Showing posts with label company. Show all posts
Showing posts with label company. Show all posts
Monday, March 12, 2012
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
Subscribe to:
Posts (Atom)