Wednesday, March 7, 2012

Only seeing dbs that have access to

Hi,
I came across a script to update the sp_MSdbuseraccess SP so that when a
user logs in to SQL Server 2000 using Enterprise Manager in SQL 2000 they
only see databases that they have access to
(http://support.microsoft.com/default.aspx/kb/889696). This works fine apart
from if users are using the new SQL Management Studio tool to connect to the
SQL 2000 server. Now they can see all databases again! Has anyone overcome
this issue?
Thanks - Kate.This is most probably not possible. SSMS doesn't use stored procedures when doing things like
querying for what databases exist (*). And there isn't anything in the 2000 engine for this. There
is in 2005, where you by default (at the engine level) only see objects that you actually have
access to (except for databases - you see all, but this is controllable though a REVOKE statement).
(*) I just ran a profiler trace to see the SQL statement submitted when expanding the databases
folder or the drop-down lost for a query windows. In both cases there was a SELECT against a system
table. This was against a 2005 instance (I don't have a setup right now where I can connect SSMS
against a 2000 instance), but I very much doubt it is any different.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kate" <Kate_w112@.hotmail.com> wrote in message news:eHC6zgc3HHA.2064@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I came across a script to update the sp_MSdbuseraccess SP so that when a user logs in to SQL
> Server 2000 using Enterprise Manager in SQL 2000 they only see databases that they have access to
> (http://support.microsoft.com/default.aspx/kb/889696). This works fine apart from if users are
> using the new SQL Management Studio tool to connect to the SQL 2000 server. Now they can see all
> databases again! Has anyone overcome this issue?
> Thanks - Kate.|||Ok great - what was the SQL statement?
thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Ozw%23nrd3HHA.2752@.TK2MSFTNGP06.phx.gbl...
> This is most probably not possible. SSMS doesn't use stored procedures
> when doing things like querying for what databases exist (*). And there
> isn't anything in the 2000 engine for this. There is in 2005, where you by
> default (at the engine level) only see objects that you actually have
> access to (except for databases - you see all, but this is controllable
> though a REVOKE statement).
> (*) I just ran a profiler trace to see the SQL statement submitted when
> expanding the databases folder or the drop-down lost for a query windows.
> In both cases there was a SELECT against a system table. This was against
> a 2005 instance (I don't have a setup right now where I can connect SSMS
> against a 2000 instance), but I very much doubt it is any different.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Kate" <Kate_w112@.hotmail.com> wrote in message
> news:eHC6zgc3HHA.2064@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I came across a script to update the sp_MSdbuseraccess SP so that when a
>> user logs in to SQL Server 2000 using Enterprise Manager in SQL 2000 they
>> only see databases that they have access to
>> (http://support.microsoft.com/default.aspx/kb/889696). This works fine
>> apart from if users are using the new SQL Management Studio tool to
>> connect to the SQL 2000 server. Now they can see all databases again! Has
>> anyone overcome this issue?
>> Thanks - Kate.
>|||you mention a REVOKE statement for SQL 2005 installs - could you elaborate
on this?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Ozw%23nrd3HHA.2752@.TK2MSFTNGP06.phx.gbl...
> This is most probably not possible. SSMS doesn't use stored procedures
> when doing things like querying for what databases exist (*). And there
> isn't anything in the 2000 engine for this. There is in 2005, where you by
> default (at the engine level) only see objects that you actually have
> access to (except for databases - you see all, but this is controllable
> though a REVOKE statement).
> (*) I just ran a profiler trace to see the SQL statement submitted when
> expanding the databases folder or the drop-down lost for a query windows.
> In both cases there was a SELECT against a system table. This was against
> a 2005 instance (I don't have a setup right now where I can connect SSMS
> against a 2000 instance), but I very much doubt it is any different.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Kate" <Kate_w112@.hotmail.com> wrote in message
> news:eHC6zgc3HHA.2064@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I came across a script to update the sp_MSdbuseraccess SP so that when a
>> user logs in to SQL Server 2000 using Enterprise Manager in SQL 2000 they
>> only see databases that they have access to
>> (http://support.microsoft.com/default.aspx/kb/889696). This works fine
>> apart from if users are using the new SQL Management Studio tool to
>> connect to the SQL 2000 server. Now they can see all databases again! Has
>> anyone overcome this issue?
>> Thanks - Kate.
>|||Basically a SELECT from sys.databases. Remember, I was running against a 2005 instance, so you won't
see the same SELECT against a 2000 instance (you will probably see some SELECT from sysdatabases).
But you can easily check this yourself using the same method I did (using Profiler).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kate" <Kate_w112@.hotmail.com> wrote in message news:eLAEWnf3HHA.464@.TK2MSFTNGP02.phx.gbl...
> Ok great - what was the SQL statement?
> thanks.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:Ozw%23nrd3HHA.2752@.TK2MSFTNGP06.phx.gbl...
>> This is most probably not possible. SSMS doesn't use stored procedures when doing things like
>> querying for what databases exist (*). And there isn't anything in the 2000 engine for this.
>> There is in 2005, where you by default (at the engine level) only see objects that you actually
>> have access to (except for databases - you see all, but this is controllable though a REVOKE
>> statement).
>> (*) I just ran a profiler trace to see the SQL statement submitted when expanding the databases
>> folder or the drop-down lost for a query windows. In both cases there was a SELECT against a
>> system table. This was against a 2005 instance (I don't have a setup right now where I can
>> connect SSMS against a 2000 instance), but I very much doubt it is any different.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Kate" <Kate_w112@.hotmail.com> wrote in message news:eHC6zgc3HHA.2064@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I came across a script to update the sp_MSdbuseraccess SP so that when a user logs in to SQL
>> Server 2000 using Enterprise Manager in SQL 2000 they only see databases that they have access
>> to (http://support.microsoft.com/default.aspx/kb/889696). This works fine apart from if users
>> are using the new SQL Management Studio tool to connect to the SQL 2000 server. Now they can see
>> all databases again! Has anyone overcome this issue?
>> Thanks - Kate.
>|||Check out the "VIEW ANY DATABASE" privilege. Try below, for example:
CREATE LOGIN x WITH PASSWORD = 'hjus8#."3'
EXECUTE AS LOGIN = 'x'
SELECT name FROM sys.databases --14 rows
REVERT
DENY VIEW ANY DATABASE TO x
EXECUTE AS LOGIN = 'x'
SELECT name FROM sys.databases --2 rows
REVERT
GO
DROP LOGIN x
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kate" <Kate_w112@.hotmail.com> wrote in message news:Oj0C6nf3HHA.5796@.TK2MSFTNGP05.phx.gbl...
> you mention a REVOKE statement for SQL 2005 installs - could you elaborate
> on this?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:Ozw%23nrd3HHA.2752@.TK2MSFTNGP06.phx.gbl...
>> This is most probably not possible. SSMS doesn't use stored procedures
>> when doing things like querying for what databases exist (*). And there
>> isn't anything in the 2000 engine for this. There is in 2005, where you by
>> default (at the engine level) only see objects that you actually have
>> access to (except for databases - you see all, but this is controllable
>> though a REVOKE statement).
>> (*) I just ran a profiler trace to see the SQL statement submitted when
>> expanding the databases folder or the drop-down lost for a query windows.
>> In both cases there was a SELECT against a system table. This was against
>> a 2005 instance (I don't have a setup right now where I can connect SSMS
>> against a 2000 instance), but I very much doubt it is any different.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Kate" <Kate_w112@.hotmail.com> wrote in message
>> news:eHC6zgc3HHA.2064@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I came across a script to update the sp_MSdbuseraccess SP so that when a
>> user logs in to SQL Server 2000 using Enterprise Manager in SQL 2000 they
>> only see databases that they have access to
>> (http://support.microsoft.com/default.aspx/kb/889696). This works fine
>> apart from if users are using the new SQL Management Studio tool to
>> connect to the SQL 2000 server. Now they can see all databases again! Has
>> anyone overcome this issue?
>> Thanks - Kate.
>

No comments:

Post a Comment