SQL Server Programming - Modifying sys.sp

Author

Modifying sys.sp_MSdbuseraccess in SQL 2005

Matt

2005-12-13, 9:59 pm

Just wondering if anyone has tried the following KB article with SQL
2005?

http://support.microsoft.com/?id=889696

Doing a diff between the sproc in 2000 and 2005 there are no changes
except in the comments, so I'm pretty sure the above KB's modified code
can apply to 2005; however, in 2005, the modified sp_MSdbuseraccess
fails to execute correctly citing that the user 'SA':

"Cannot drop the procedure 'sp_MSdbuseraccess', because it does not
exist or you do not have permission."

I've tried dropping SQL into single-user mode and this didn't allow me
to make changes to that sproc. Additionally, if I attempt to do a
direct modify via the Management Studio I get the error:

"Invalid object name 'sys.sp_MSdbuseracccess'.

when I execute the ALTER query.

Does anyone out there have any insight into this? Any help would be
much appreciated. Thanks!

-matt

Dan Guzman

2005-12-13, 9:59 pm

> Just wondering if anyone has tried the following KB article with SQL
> 2005?
>
> http://support.microsoft.com/?id=889696

Why would you want to do this? The article addresses a problem with
Enterprise Manager, which is a non-issue since SQL Server Management Studio
should be used for 2005 instances.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Matt" <baldwinmathew@gmail.com> wrote in message
news:1134524783.030331.249130@g14g2000cwa.googlegroups.com...
> Just wondering if anyone has tried the following KB article with SQL
> 2005?
>
> http://support.microsoft.com/?id=889696
>
> Doing a diff between the sproc in 2000 and 2005 there are no changes
> except in the comments, so I'm pretty sure the above KB's modified code
> can apply to 2005; however, in 2005, the modified sp_MSdbuseraccess
> fails to execute correctly citing that the user 'SA':
>
> "Cannot drop the procedure 'sp_MSdbuseraccess', because it does not
> exist or you do not have permission."
>
> I've tried dropping SQL into single-user mode and this didn't allow me
> to make changes to that sproc. Additionally, if I attempt to do a
> direct modify via the Management Studio I get the error:
>
> "Invalid object name 'sys.sp_MSdbuseracccess'.
>
>
> when I execute the ALTER query.
>
> Does anyone out there have any insight into this? Any help would be
> much appreciated. Thanks!
>
> -matt
>

Matt Baldwin

2005-12-13, 9:59 pm

Hi Dan:

Thanks for the reply.

The reason I'm doing this is to hide the databases a user doesn't have
access to through SQL Server Management Studio. The above article
accomplished this in SQL 2000. If you log on through Management Studio
and only have access to db A, but there are dbs A - Z, you can still
see all databases regardless of your access rights to those databases.

This is handy in a hosting scenario with a shared SQL server where you
have mutliple customer dbs. You don't want to expose the whole list to
a logged on user, only the db they have access to.

How would I go about altering the MSdbuseraccess sproc to accomplish
this? All methods I have tried won't allow me to change that sproc.

Thanks.

-matt

Dan Guzman

2005-12-13, 9:59 pm

> How would I go about altering the MSdbuseraccess sproc to accomplish
> this? All methods I have tried won't allow me to change that sproc.

SSMS does not use that proc so changing it wouldn't help you. Looking at a
Profiler trace, it looks like the database enum done with a straight query.
I'll see if I can find out more info.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Matt Baldwin" <baldwinmathew@gmail.com> wrote in message
news:1134530152.418502.120570@g14g2000cwa.googlegroups.com...
> Hi Dan:
>
> Thanks for the reply.
>
> The reason I'm doing this is to hide the databases a user doesn't have
> access to through SQL Server Management Studio. The above article
> accomplished this in SQL 2000. If you log on through Management Studio
> and only have access to db A, but there are dbs A - Z, you can still
> see all databases regardless of your access rights to those databases.
>
> This is handy in a hosting scenario with a shared SQL server where you
> have mutliple customer dbs. You don't want to expose the whole list to
> a logged on user, only the db they have access to.
>
> How would I go about altering the MSdbuseraccess sproc to accomplish
> this? All methods I have tried won't allow me to change that sproc.
>
> Thanks.
>
> -matt
>

Matt Baldwin

2005-12-13, 9:59 pm

Ah, OK. That's interesting. I would have thought that a sproc would
have been used to populate that list versus a straight query. Any info
you can dig up would be greatly appreciated.

Thanks.

-matt

Dan Guzman

2005-12-14, 7:09 pm

In 2005, you can hide databases that users don't have permission to access
by denying VIEW ANY DATABASE:

USE master
DENY VIEW ANY DATABASE TO SomeLogin

FYI, the profile trace showed that sys.databases was used as the source for
the list. No surprise here but reviewing the Books Online:

<Excerpt
href="ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/46c288c1-3410-4d68-a027-3bbf33239289.htm">
If the caller of sys.databases is not the owner of the database and the
database is not master or tempdb, the minimum permissions required to see
the corresponding row are ALTER ANY DATABASE or VIEW ANY DATABASE
server-level permission, or CREATE DATABASE permission in the master
database.
</Excerpt>

As you probably know, there are many security changes in SQL 2005. The
model is so much more robust you should probably take the time to thoroughly
review the security items in the BOL. Many of the enhancements were
implemented specifically for shared environments like yours that need
granular security. I think I need to peruse the BOL too :-)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Matt Baldwin" <baldwinmathew@gmail.com> wrote in message
news:1134532343.895521.197690@g14g2000cwa.googlegroups.com...
> Ah, OK. That's interesting. I would have thought that a sproc would
> have been used to populate that list versus a straight query. Any info
> you can dig up would be greatly appreciated.
>
> Thanks.
>
> -matt
>

Matt Baldwin

2005-12-14, 7:09 pm

Thanks for the info Dan.

Yeah, looking through SQL 2005's online resources -- there has been
quite a few changes to the security model. Much, much, more granular
as you said. I think I'll take your advice and bring my laptop over
xmas break and do some reading.

At any rate, thanks again for your help.

-matt

Dan Guzman

2005-12-14, 7:09 pm

I'm glad I was able to help you out, Matt.

I found that VIEW ANY DATABASE is inherited from the 'public' server-level
principal. Consequently, instead of DENY, you could REVOKE the permission
from 'public' in the master database so that you don't need to subsequently
DENY. However, you would then need to GRANT the permission to those who
need to enumerate all databases, including sy

min role members. I don't
know if there are other implications so additional testing would be in
order.

By the way, Kalen Delaney told me that she mentions VIEW ANY DATABASE in her
upcoming MSDN magazine article on data visibility. I look forward to
reading it.

--
Dan Guzman
SQL Server MVP

"Matt Baldwin" <baldwinmathew@gmail.com> wrote in message
news:1134579988.315547.43690@g14g2000cwa.googlegroups.com...
> Thanks for the info Dan.
>
> Yeah, looking through SQL 2005's online resources -- there has been
> quite a few changes to the security model. Much, much, more granular
> as you said. I think I'll take your advice and bring my laptop over
> xmas break and do some reading.
>
> At any rate, thanks again for your help.
>
> -matt
>

Matt Baldwin

2005-12-14, 7:09 pm

Hi Dan:

One thing of interest, as I mentioned earlier, is that most hosts used
that KB article to hide customer databases from each other; however,
going on the premise that Management Studio does a direct query for the
databases versus calling that sproc, then those databases will be
exposed to Management Studio. Upon testing, I confirmed this
suspicion. When you connect to a SQL 2000 instance with Management
Studio you can view all databases regardless of if you have access to
them or not. This won't be good for webhosts out there from a security
standpoint.

It would have been nice if, upon connecting to SQL 2000, Management
Studio would have mimicked Enterprise Manager with the way it pulls the
list of databases.

Just an FYI. :) Thought I'd contribute back to the knowledge sharing.

-matt

Dan Guzman

2005-12-14, 7:09 pm

> It would have been nice if, upon connecting to SQL 2000, Management
> Studio would have mimicked Enterprise Manager with the way it pulls the
> list of databases.

This certainly seems like a reasonable product suggestion. You can submit
product feedback directly at http://lab.msdn.microsoft.com/productfeedback/

BTW, I ran across the following in the BOL. Although the revoke from public
method I mentioned might work, it's usually best to stick with documented
practices.

<Excerpt
href="ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/88ab9837-b707-43a9-8926-3e964f5a8b08.htm">

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

</Excerpt>

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Matt Baldwin" <baldwinmathew@gmail.com> wrote in message
news:1134600267.599868.255390@g49g2000cwa.googlegroups.com...
> Hi Dan:
>
> One thing of interest, as I mentioned earlier, is that most hosts used
> that KB article to hide customer databases from each other; however,
> going on the premise that Management Studio does a direct query for the
> databases versus calling that sproc, then those databases will be
> exposed to Management Studio. Upon testing, I confirmed this
> suspicion. When you connect to a SQL 2000 instance with Management
> Studio you can view all databases regardless of if you have access to
> them or not. This won't be good for webhosts out there from a security
> standpoint.
>
> It would have been nice if, upon connecting to SQL 2000, Management
> Studio would have mimicked Enterprise Manager with the way it pulls the
> list of databases.
>
> Just an FYI. :) Thought I'd contribute back to the knowledge sharing.
>
> -matt
>

Matt Baldwin

2005-12-14, 10:02 pm

Done. Just sent the suggestion in.

I'm almost done with my SQL script that will create a shared SQL 2005
database within a hosted environment. I basically stick with DENY VIEW
ANY DATABASE. This seems to work. The only issue I'm running into now
is I'm trying to figure out how to deny the dbowner the ability to do
ADD FILE so they are unable to create any .NDF files outside of their
already created .MDF. I plan on pouring through the BOL some more
tomorrow to see if I can discover a way of restricting that.

-matt

Matt Baldwin

2005-12-15, 7:06 pm

Incidentally, I'm in the process of porting what I'm doing in SQL
statements to C# using SMO. The below will deny the user the VIEW ANY
DATABASE permission:

Server srv = new Server();
ServerPermissionSet DenyViewAnyDatabase = new
ServerPermissionSet(ServerPermission.ViewAnyDatabase);

// This basically stops them from seeing all the databases on the
server except for their own.
srv.Deny(DenyViewAnyDatabase, username);

-matt

clifford.dibble@gmail.com

2005-12-15, 9:59 pm

Hi,

Trying to "hide" users databases from those who cannot access them is
problematic because to fully evaluate this predicate, you need to open
up the database itself to check to see if a user is provisioned in the
database and has CONNECT permission.

There are two big problems with this approach:

1. It is very time consuming. Opening up a database is a costly
operation. So in the "hosting scenario" with 10s or 100s of
databases, this really becomes a bottleneck.

2. It can give you incorrect/inconslusive results if the database is
OFFLINE or otherwise not accessible.

We got so many complaints about both (1) and (2) during the course of
Yukon development, we choose to de-couple the "database visibility"
check from the database itself. Hence, the server-level VIEW ANY
DATABASE permission. As the BOL and Kalen's upcoming article
explains, you can REVOKE that permission to restrict visibilty.

BTW, the reason you cannot DROP and re-CREATE the system sprocs is
because they don't physically reside in master database anymore.
Search the BOL for "resource database."

Kind regards,
Clifford Dibble [MSFT]

Dan Guzman wrote:[color=darkred]
>
> This certainly seems like a reasonable product suggestion. You can submit
> product feedback directly at http://lab.msdn.microsoft.com/productfeedback/
>
> BTW, I ran across the following in the BOL. Although the revoke from public
> method I mentioned might work, it's usually best to stick with documented
> practices.
>
> <Excerpt
> href="ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/88ab9837-b707-43a9-8926-3e964f5a8b08.htm">
>
> 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
>
> </Excerpt>
>
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Matt Baldwin" <baldwinmathew@gmail.com> wrote in message
> news:1134600267.599868.255390@g49g2000cwa.googlegroups.com...

Dan Guzman

2005-12-16, 7:09 pm

Thanks a lot for the info, Clifford.

--
Dan Guzman
SQL Server MVP

<clifford.dibble@gmail.com> wrote in message
news:1134705010.620587.160020@g14g2000cwa.googlegroups.com...
> Hi,
>
> Trying to "hide" users databases from those who cannot access them is
> problematic because to fully evaluate this predicate, you need to open
> up the database itself to check to see if a user is provisioned in the
> database and has CONNECT permission.
>
> There are two big problems with this approach:
>
> 1. It is very time consuming. Opening up a database is a costly
> operation. So in the "hosting scenario" with 10s or 100s of
> databases, this really becomes a bottleneck.
>
> 2. It can give you incorrect/inconslusive results if the database is
> OFFLINE or otherwise not accessible.
>
> We got so many complaints about both (1) and (2) during the course of
> Yukon development, we choose to de-couple the "database visibility"
> check from the database itself. Hence, the server-level VIEW ANY
> DATABASE permission. As the BOL and Kalen's upcoming article
> explains, you can REVOKE that permission to restrict visibilty.
>
> BTW, the reason you cannot DROP and re-CREATE the system sprocs is
> because they don't physically reside in master database anymore.
> Search the BOL for "resource database."
>
> Kind regards,
> Clifford Dibble [MSFT]
>
>
> Dan Guzman wrote:
>

Matt Baldwin

2005-12-16, 7:09 pm

Thanks Clifford for the additional info.

-matt

Sponsored Links