I am doing some security work on SQL server and am concerned about the permi
sions on the public role. i want to strip down the permisions given to the
public role in the system databases. What are the implications of runnign sc
ripts to revoke all to publ
ic for all statements and objects in all databases. I have read this causes
problems? What have other folk done regarding this and is there a definitive
list anywhere?
Thanks in advanceGemmill,
We have many databases with all rights to user tables, views, stored
procedures, etc. revoked for public. (You can do this by simply never
granting any rights to public as well.) Also, make sure you do not have
the guest account. (I have never tried to revoke public rights to the
system tables, so I don't know if you can do that. I guess I can go try
when I get back to my office.)
The main implication is that you will break rights that your people have
become accustomed to.
Russell Fields
"Gemmill" <anonymous@.discussions.microsoft.com> wrote in message
news:2657C8D7-F49D-47D1-99B6-04D470BE6D71@.microsoft.com...
> I am doing some security work on SQL server and am concerned about the
permisions on the public role. i want to strip down the permisions given to
the public role in the system databases. What are the implications of
runnign scripts to revoke all to public for all statements and objects in
all databases. I have read this causes problems? What have other folk done
regarding this and is there a definitive list anywhere?
> Thanks in advance|||Thanks for the reply russell. Iam fairly comforatable with removing permissi
ons as susggested from user tables. But am concerned it will cause problems
if i carry it out on the system databases. I would like to "lock down" publi
c permissions on the system
databases without seriously affecting system operation. What have you done i
n regards to this.
Thanks|||My recommendation would be to leave the system tables alone. If there
are any security holes that turn up that arise from granting
permissions to public, then they'll probably be addressed in a service
pack, if they haven't been already. If you try mucking around in the
systems tables yourself, you'll probably just end up breaking things.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Fri, 27 Feb 2004 01:11:05 -0800, "gemmill"
<anonymous@.discussions.microsoft.com> wrote:
>Thanks for the reply russell. Iam fairly comforatable with removing permissions as
susggested from user tables. But am concerned it will cause problems if i carry it o
ut on the system databases. I would like to "lock down" public permissions on the sy
ste
m databases without seriously affecting system operation. What have you done in regards to
this.
>Thanks|||Best practices often tell you to remove PUBLIC role from all user objects an
d this should be done after a careful review of the permission required by y
our application.
Some experts also go as far as to recommend that you remove PUBLIC role perm
issions from system stored procedures and extended stored procedures to prev
ent attacks on your server. This should also be done, but only after carefu
l testing as removing PUBLI
C permissions to all stored procedures can often break some functionality in
Enterprise Manager. I do not know of a article/whitepaper that has studied
this and listed what exactly will be broken based on each system stored proc
edure.
Brian Kelley recently released a nice whitepaper on the PUBLIC role permissi
ons to system tables that you should read to see if it is any help.
SQL Server 2000: Permissions on System Tables Granted to Logins Due to the P
ublic Role
http://www.giac.org./practical/GSEC...Kelley_GSEC.pdf
Randy Dyess
www.Database-Security.Info|||Thanks for the reply's, much appreciated
Showing posts with label public. Show all posts
Showing posts with label public. Show all posts
Wednesday, March 21, 2012
Revoking permissions on system tables to the public role.
We have an audit issue that is requiring me to revoke the select permissions from the public role to the system tables. Has anyone had to do this?? What problems did you experience? Are their any tables that you were not able to change the permissions on? Any help is greatly appreciated...when you say "system tables" which ones...system tables reside within databases and of course within master,model,msdb, etc. ...
...how are the users you want to restrict accessing the system tables?? (an application, queries, report writer, etc. ...)
...as to what affect revoking system table access from the public role will have, you would first have to answer the above questions...
...also you state this is for auditing purposes, if you prove that you are unable to maintain functionality after revoking permissions are they going to allow an exception?|||..when you say "system tables" which ones...system tables reside within databases and of course within master,model,msdb, etc. ...
--I mean all. From the master, msdb, and user databases. My concerns are mostly with the master and msdb databases.
...how are the users you want to restrict accessing the system tables?? (an application, queries, report writer, etc. ...)
--We use several types of applications. Everything from web pages( We are a web company), to PeopleSoft, Siebel, and Access.
also you state this is for auditing purposes, if you prove that you are unable to maintain functionality after revoking permissions are they going to allow an exception?
-- I am not sure, but I believe that they will. This is related to the new Sarbanes-Oxley act.|||...we are working on Sarbanes as well...but there has been nothing to compell us to look at this type of restriction...
...I am not sure you can restrict this access and still have application functionality...
...if this is still something you would like to pursue, I would start at the database level, create a test database and userid's and a method of access.
First you would have to test to ensure the access method works for the objects created in the database. Once that was proven, I would try the revoke against the databases system tables. To obtain a list of system tables:
select name from sysobjects where type = 'S'
you can then use the "sp_helprotect" stored procedure to obtain the current security settings.
then use the REVOKE command to revoke public access from these objects...
...then the access methid would have to be tested again...
now as far as the system databases goes, i'm sure that revoking public access would allow funtionality to remain...but you'd have to follow a similiar test...good luck...|||Why on earth would SOX be interested in the system tables? Are you storing SOX-sensitive data in the system tables? Using system tables for storing user data is a really bad idea!
-PatP|||...we discussed SOX at lunch and agree that everyone seems to be going overboard in the name of SOX...including auditors who are just guessing at how to comply with the new expectations...(not that they ever knew!)...
...how are the users you want to restrict accessing the system tables?? (an application, queries, report writer, etc. ...)
...as to what affect revoking system table access from the public role will have, you would first have to answer the above questions...
...also you state this is for auditing purposes, if you prove that you are unable to maintain functionality after revoking permissions are they going to allow an exception?|||..when you say "system tables" which ones...system tables reside within databases and of course within master,model,msdb, etc. ...
--I mean all. From the master, msdb, and user databases. My concerns are mostly with the master and msdb databases.
...how are the users you want to restrict accessing the system tables?? (an application, queries, report writer, etc. ...)
--We use several types of applications. Everything from web pages( We are a web company), to PeopleSoft, Siebel, and Access.
also you state this is for auditing purposes, if you prove that you are unable to maintain functionality after revoking permissions are they going to allow an exception?
-- I am not sure, but I believe that they will. This is related to the new Sarbanes-Oxley act.|||...we are working on Sarbanes as well...but there has been nothing to compell us to look at this type of restriction...
...I am not sure you can restrict this access and still have application functionality...
...if this is still something you would like to pursue, I would start at the database level, create a test database and userid's and a method of access.
First you would have to test to ensure the access method works for the objects created in the database. Once that was proven, I would try the revoke against the databases system tables. To obtain a list of system tables:
select name from sysobjects where type = 'S'
you can then use the "sp_helprotect" stored procedure to obtain the current security settings.
then use the REVOKE command to revoke public access from these objects...
...then the access methid would have to be tested again...
now as far as the system databases goes, i'm sure that revoking public access would allow funtionality to remain...but you'd have to follow a similiar test...good luck...|||Why on earth would SOX be interested in the system tables? Are you storing SOX-sensitive data in the system tables? Using system tables for storing user data is a really bad idea!
-PatP|||...we discussed SOX at lunch and agree that everyone seems to be going overboard in the name of SOX...including auditors who are just guessing at how to comply with the new expectations...(not that they ever knew!)...
Tuesday, March 20, 2012
Revoking Permissions
I am going to tighten permissions on my SQL Server. In one book, it is reco
mmended to remove all permissions on the public role to user databases. By
default, it looks like the public role is granted permissions to the system
tables and system stored pr
ocedures.
Does the public role need access to these?
Thank you,
JLFlemingBooks Online has this recommendation:
To protect against unauthorized data access, minimize the permissions
granted to the public role. Instead, grant permissions to other database
roles and to user accounts associated with logins
There's some additional guidance here:
SQL Server 2000 SP3 Security Features and Best Practices: Security Best
Practices Checklist
http://www.microsoft.com/technet/pr...n/sp3sec04.mspx
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
mmended to remove all permissions on the public role to user databases. By
default, it looks like the public role is granted permissions to the system
tables and system stored pr
ocedures.
Does the public role need access to these?
Thank you,
JLFlemingBooks Online has this recommendation:
To protect against unauthorized data access, minimize the permissions
granted to the public role. Instead, grant permissions to other database
roles and to user accounts associated with logins
There's some additional guidance here:
SQL Server 2000 SP3 Security Features and Best Practices: Security Best
Practices Checklist
http://www.microsoft.com/technet/pr...n/sp3sec04.mspx
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
revoking all the privileges from public
I am revoking all the privileges from public role from master and tempdb
database, will that be any thing wrong after that? just want expert idea
because I ran one tool and it gave me suggession to remove public rights
from system objects on master database.
LIke following are some of the commands
USE MASTER
REVOKE EXEC ON dt_droppropertiesbyid FROM PUBLIC
GO
REVOKE EXEC ON dt_checkinobject FROM PUBLIC
GO
REVOKE SELECT ON dtproperties FROM PUBLIC
GO
REVOKE EXEC ON dt_checkoutobject FROM PUBLIC
GO
REVOKE EXEC ON dt_checkoutobject_u FROM PUBLIC
GO
Please give any suggession,
Rogers,
Do not revoke all rights from public for these databases. That will break
many things.
RLF
"Rogers" <naissani@.hotmail.com> wrote in message
news:eLXU7qB4HHA.5804@.TK2MSFTNGP05.phx.gbl...
>I am revoking all the privileges from public role from master and tempdb
>database, will that be any thing wrong after that? just want expert idea
>because I ran one tool and it gave me suggession to remove public rights
>from system objects on master database.
> LIke following are some of the commands
> USE MASTER
> REVOKE EXEC ON dt_droppropertiesbyid FROM PUBLIC
> GO
> REVOKE EXEC ON dt_checkinobject FROM PUBLIC
> GO
> REVOKE SELECT ON dtproperties FROM PUBLIC
> GO
> REVOKE EXEC ON dt_checkoutobject FROM PUBLIC
> GO
> REVOKE EXEC ON dt_checkoutobject_u FROM PUBLIC
> GO
>
> Please give any suggession,
>
|||Thanks alot for your reply,
Is there any Microsoft article on that so I can present this to our Manager
and also refer that Microsoft doesn't recommend that.
Thanks
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eMITWCD4HHA.5776@.TK2MSFTNGP04.phx.gbl...
> Rogers,
> Do not revoke all rights from public for these databases. That will break
> many things.
> RLF
> "Rogers" <naissani@.hotmail.com> wrote in message
> news:eLXU7qB4HHA.5804@.TK2MSFTNGP05.phx.gbl...
>
|||Rogers,
You don't say which version of SQL Server you are running, which may make
some difference.
First of all, you can revoke some rights in master and msdb and still have
the necessary things work, but revoking everything would be a disaster.
Basic system stored procedures that everyone needs would also be revoked.
You need to be more measured in your revoking of rights, which means you
need to understand what is still needed and what can be safely revoke. (If
you are using SQL Server 2005, note that some rights that were exposed in
SQL Server 2000 are now more tightly guarded.)
Here is a PowerPoint SQL Server 2000 oriented from about 3 years ago, which
does suggest some areas for rights to be removed:
http://download.microsoft.com/download/7/3/c/73ca9891-f3eb-4caf-bf60-51f4aca51706/DBA5_kburge.ppt#302,43,URLs
Tools such as AuditPro also make suggestions.
So, I do not have a specific list for you, review the things in the BOL (or
do "exec sp_helprotect @.username='public'" in both master and msdb) to see
what is granted, then think about whether you want that or not. (It is
pointless to manipulate rights in tempdb, since it is rebuilt everytime the
SQL Server restarts and it has its own mechanism for privacy.)
I tried finding a real solid article on this, but (alas) could not.
RLF
Note: The following article offers no guidance on what to revoke and what
not to revoke, it merely tells you what you can do, not what it is wise to
do: http://support.microsoft.com/kb/75291
"Rogers" <naissani@.hotmail.com> wrote in message
news:Oqsq$FD4HHA.3900@.TK2MSFTNGP02.phx.gbl...
> Thanks alot for your reply,
> Is there any Microsoft article on that so I can present this to our
> Manager and also refer that Microsoft doesn't recommend that.
> Thanks
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:eMITWCD4HHA.5776@.TK2MSFTNGP04.phx.gbl...
>
|||Thanks alot, I am using SQL Server 2000.
Thanks
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:u5hPS6D4HHA.3916@.TK2MSFTNGP02.phx.gbl...
> Rogers,
> You don't say which version of SQL Server you are running, which may make
> some difference.
> First of all, you can revoke some rights in master and msdb and still have
> the necessary things work, but revoking everything would be a disaster.
> Basic system stored procedures that everyone needs would also be revoked.
> You need to be more measured in your revoking of rights, which means you
> need to understand what is still needed and what can be safely revoke.
> (If you are using SQL Server 2005, note that some rights that were exposed
> in SQL Server 2000 are now more tightly guarded.)
> Here is a PowerPoint SQL Server 2000 oriented from about 3 years ago,
> which does suggest some areas for rights to be removed:
> http://download.microsoft.com/download/7/3/c/73ca9891-f3eb-4caf-bf60-51f4aca51706/DBA5_kburge.ppt#302,43,URLs
> Tools such as AuditPro also make suggestions.
> So, I do not have a specific list for you, review the things in the BOL
> (or do "exec sp_helprotect @.username='public'" in both master and msdb) to
> see what is granted, then think about whether you want that or not. (It
> is pointless to manipulate rights in tempdb, since it is rebuilt everytime
> the SQL Server restarts and it has its own mechanism for privacy.)
> I tried finding a real solid article on this, but (alas) could not.
> RLF
> Note: The following article offers no guidance on what to revoke and what
> not to revoke, it merely tells you what you can do, not what it is wise to
> do: http://support.microsoft.com/kb/75291
>
> "Rogers" <naissani@.hotmail.com> wrote in message
> news:Oqsq$FD4HHA.3900@.TK2MSFTNGP02.phx.gbl...
>
|||Please send me the list If you find any like which system objects in Master
and MSDB can be revoked from Public Role, and no impact on sql server.
Thanks alot for your help.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:u5hPS6D4HHA.3916@.TK2MSFTNGP02.phx.gbl...
> Rogers,
> You don't say which version of SQL Server you are running, which may make
> some difference.
> First of all, you can revoke some rights in master and msdb and still have
> the necessary things work, but revoking everything would be a disaster.
> Basic system stored procedures that everyone needs would also be revoked.
> You need to be more measured in your revoking of rights, which means you
> need to understand what is still needed and what can be safely revoke.
> (If you are using SQL Server 2005, note that some rights that were exposed
> in SQL Server 2000 are now more tightly guarded.)
> Here is a PowerPoint SQL Server 2000 oriented from about 3 years ago,
> which does suggest some areas for rights to be removed:
> http://download.microsoft.com/download/7/3/c/73ca9891-f3eb-4caf-bf60-51f4aca51706/DBA5_kburge.ppt#302,43,URLs
> Tools such as AuditPro also make suggestions.
> So, I do not have a specific list for you, review the things in the BOL
> (or do "exec sp_helprotect @.username='public'" in both master and msdb) to
> see what is granted, then think about whether you want that or not. (It
> is pointless to manipulate rights in tempdb, since it is rebuilt everytime
> the SQL Server restarts and it has its own mechanism for privacy.)
> I tried finding a real solid article on this, but (alas) could not.
> RLF
> Note: The following article offers no guidance on what to revoke and what
> not to revoke, it merely tells you what you can do, not what it is wise to
> do: http://support.microsoft.com/kb/75291
>
> "Rogers" <naissani@.hotmail.com> wrote in message
> news:Oqsq$FD4HHA.3900@.TK2MSFTNGP02.phx.gbl...
>
database, will that be any thing wrong after that? just want expert idea
because I ran one tool and it gave me suggession to remove public rights
from system objects on master database.
LIke following are some of the commands
USE MASTER
REVOKE EXEC ON dt_droppropertiesbyid FROM PUBLIC
GO
REVOKE EXEC ON dt_checkinobject FROM PUBLIC
GO
REVOKE SELECT ON dtproperties FROM PUBLIC
GO
REVOKE EXEC ON dt_checkoutobject FROM PUBLIC
GO
REVOKE EXEC ON dt_checkoutobject_u FROM PUBLIC
GO
Please give any suggession,
Rogers,
Do not revoke all rights from public for these databases. That will break
many things.
RLF
"Rogers" <naissani@.hotmail.com> wrote in message
news:eLXU7qB4HHA.5804@.TK2MSFTNGP05.phx.gbl...
>I am revoking all the privileges from public role from master and tempdb
>database, will that be any thing wrong after that? just want expert idea
>because I ran one tool and it gave me suggession to remove public rights
>from system objects on master database.
> LIke following are some of the commands
> USE MASTER
> REVOKE EXEC ON dt_droppropertiesbyid FROM PUBLIC
> GO
> REVOKE EXEC ON dt_checkinobject FROM PUBLIC
> GO
> REVOKE SELECT ON dtproperties FROM PUBLIC
> GO
> REVOKE EXEC ON dt_checkoutobject FROM PUBLIC
> GO
> REVOKE EXEC ON dt_checkoutobject_u FROM PUBLIC
> GO
>
> Please give any suggession,
>
|||Thanks alot for your reply,
Is there any Microsoft article on that so I can present this to our Manager
and also refer that Microsoft doesn't recommend that.
Thanks
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eMITWCD4HHA.5776@.TK2MSFTNGP04.phx.gbl...
> Rogers,
> Do not revoke all rights from public for these databases. That will break
> many things.
> RLF
> "Rogers" <naissani@.hotmail.com> wrote in message
> news:eLXU7qB4HHA.5804@.TK2MSFTNGP05.phx.gbl...
>
|||Rogers,
You don't say which version of SQL Server you are running, which may make
some difference.
First of all, you can revoke some rights in master and msdb and still have
the necessary things work, but revoking everything would be a disaster.
Basic system stored procedures that everyone needs would also be revoked.
You need to be more measured in your revoking of rights, which means you
need to understand what is still needed and what can be safely revoke. (If
you are using SQL Server 2005, note that some rights that were exposed in
SQL Server 2000 are now more tightly guarded.)
Here is a PowerPoint SQL Server 2000 oriented from about 3 years ago, which
does suggest some areas for rights to be removed:
http://download.microsoft.com/download/7/3/c/73ca9891-f3eb-4caf-bf60-51f4aca51706/DBA5_kburge.ppt#302,43,URLs
Tools such as AuditPro also make suggestions.
So, I do not have a specific list for you, review the things in the BOL (or
do "exec sp_helprotect @.username='public'" in both master and msdb) to see
what is granted, then think about whether you want that or not. (It is
pointless to manipulate rights in tempdb, since it is rebuilt everytime the
SQL Server restarts and it has its own mechanism for privacy.)
I tried finding a real solid article on this, but (alas) could not.
RLF
Note: The following article offers no guidance on what to revoke and what
not to revoke, it merely tells you what you can do, not what it is wise to
do: http://support.microsoft.com/kb/75291
"Rogers" <naissani@.hotmail.com> wrote in message
news:Oqsq$FD4HHA.3900@.TK2MSFTNGP02.phx.gbl...
> Thanks alot for your reply,
> Is there any Microsoft article on that so I can present this to our
> Manager and also refer that Microsoft doesn't recommend that.
> Thanks
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:eMITWCD4HHA.5776@.TK2MSFTNGP04.phx.gbl...
>
|||Thanks alot, I am using SQL Server 2000.
Thanks
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:u5hPS6D4HHA.3916@.TK2MSFTNGP02.phx.gbl...
> Rogers,
> You don't say which version of SQL Server you are running, which may make
> some difference.
> First of all, you can revoke some rights in master and msdb and still have
> the necessary things work, but revoking everything would be a disaster.
> Basic system stored procedures that everyone needs would also be revoked.
> You need to be more measured in your revoking of rights, which means you
> need to understand what is still needed and what can be safely revoke.
> (If you are using SQL Server 2005, note that some rights that were exposed
> in SQL Server 2000 are now more tightly guarded.)
> Here is a PowerPoint SQL Server 2000 oriented from about 3 years ago,
> which does suggest some areas for rights to be removed:
> http://download.microsoft.com/download/7/3/c/73ca9891-f3eb-4caf-bf60-51f4aca51706/DBA5_kburge.ppt#302,43,URLs
> Tools such as AuditPro also make suggestions.
> So, I do not have a specific list for you, review the things in the BOL
> (or do "exec sp_helprotect @.username='public'" in both master and msdb) to
> see what is granted, then think about whether you want that or not. (It
> is pointless to manipulate rights in tempdb, since it is rebuilt everytime
> the SQL Server restarts and it has its own mechanism for privacy.)
> I tried finding a real solid article on this, but (alas) could not.
> RLF
> Note: The following article offers no guidance on what to revoke and what
> not to revoke, it merely tells you what you can do, not what it is wise to
> do: http://support.microsoft.com/kb/75291
>
> "Rogers" <naissani@.hotmail.com> wrote in message
> news:Oqsq$FD4HHA.3900@.TK2MSFTNGP02.phx.gbl...
>
|||Please send me the list If you find any like which system objects in Master
and MSDB can be revoked from Public Role, and no impact on sql server.
Thanks alot for your help.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:u5hPS6D4HHA.3916@.TK2MSFTNGP02.phx.gbl...
> Rogers,
> You don't say which version of SQL Server you are running, which may make
> some difference.
> First of all, you can revoke some rights in master and msdb and still have
> the necessary things work, but revoking everything would be a disaster.
> Basic system stored procedures that everyone needs would also be revoked.
> You need to be more measured in your revoking of rights, which means you
> need to understand what is still needed and what can be safely revoke.
> (If you are using SQL Server 2005, note that some rights that were exposed
> in SQL Server 2000 are now more tightly guarded.)
> Here is a PowerPoint SQL Server 2000 oriented from about 3 years ago,
> which does suggest some areas for rights to be removed:
> http://download.microsoft.com/download/7/3/c/73ca9891-f3eb-4caf-bf60-51f4aca51706/DBA5_kburge.ppt#302,43,URLs
> Tools such as AuditPro also make suggestions.
> So, I do not have a specific list for you, review the things in the BOL
> (or do "exec sp_helprotect @.username='public'" in both master and msdb) to
> see what is granted, then think about whether you want that or not. (It
> is pointless to manipulate rights in tempdb, since it is rebuilt everytime
> the SQL Server restarts and it has its own mechanism for privacy.)
> I tried finding a real solid article on this, but (alas) could not.
> RLF
> Note: The following article offers no guidance on what to revoke and what
> not to revoke, it merely tells you what you can do, not what it is wise to
> do: http://support.microsoft.com/kb/75291
>
> "Rogers" <naissani@.hotmail.com> wrote in message
> news:Oqsq$FD4HHA.3900@.TK2MSFTNGP02.phx.gbl...
>
Revoke View any database from public in MSSQL 2005
Hello Everyone,
I am running into an issue when I run the following query to revoke access
to all the SQL 2005 users from other DB.s
use master
Revoke View any database from public
Go
When the users logs in with SMSE he/she does not see the db under “databas
es”.
They can only access via QUERY.
Thanks,
-CarlosDoesn't it make sense, after all, you removed (REVOKE) the ability to see
(VIEW) any (that means ALL) database to everyone (PUBLIC).
Perhaps you had a different outcome in mind, and you would like some help.
But you're going to have to be more specific, It's a waste of time to try to
guess what you want as a final outcome.
Your question/request is not clear.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Carlos Caneja" <Carlos Caneja@.discussions.microsoft.com> wrote in message
news:A446C59B-C7BA-4CBF-B158-D3F88BD24434@.microsoft.com...
> Hello Everyone,
> I am running into an issue when I run the following query to revoke access
> to all the SQL 2005 users from other DB.s
> use master
> Revoke View any database from public
> Go
> When the users logs in with SMSE he/she does not see the db under
> "databases".
> They can only access via QUERY.
> Thanks,
> -Carlos
>|||Arnie,
Sorry for not being so clear on this. I will try to explaing in a different
way.
We run a shared hosting enviroment and SQL 2005 out of the box let's users
view all the db's and logins on the server when using SMSE.
By revoking the view command the users should still be able to see their
DB's since their role to each is "DBO".
As I said on the post, they can run queriues against the db using SMSE but
the tool will not graphicaly show you the db as it shows Master and tempdb.
Perhaps this will also help you understand my issue a big better.
http://weblogs.asp.net/hosterposter.../17/443118.aspx
Thanks for the reply,
-Carlos
"Arnie Rowland" wrote:
> Doesn't it make sense, after all, you removed (REVOKE) the ability to see
> (VIEW) any (that means ALL) database to everyone (PUBLIC).
> Perhaps you had a different outcome in mind, and you would like some help.
> But you're going to have to be more specific, It's a waste of time to try
to
> guess what you want as a final outcome.
> Your question/request is not clear.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Carlos Caneja" <Carlos Caneja@.discussions.microsoft.com> wrote in message
> news:A446C59B-C7BA-4CBF-B158-D3F88BD24434@.microsoft.com...
>
>|||You mention they should see the databases "since their role
to each is "DBO".
The behavior will work as the article describes but I'm not
sure what you mean by their role to each is dbo. If you mean
adding the user to db_owner role, that won't do it. The user
needs to be mapped to dbo - as in be the owner of the
database. There is a difference.
Look at who the owner of the database is by executing
sp_helpdb 'YourDatabase'
or execute
sp_helpuser 'dbo'
-Sue
On Tue, 26 Sep 2006 13:36:02 -0700, Carlos Caneja
<CarlosCaneja@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Arnie,
>Sorry for not being so clear on this. I will try to explaing in a differen
t
>way.
>We run a shared hosting enviroment and SQL 2005 out of the box let's users
>view all the db's and logins on the server when using SMSE.
>By revoking the view command the users should still be able to see their
>DB's since their role to each is "DBO".
>As I said on the post, they can run queriues against the db using SMSE but
>the tool will not graphicaly show you the db as it shows Master and tempdb.
>Perhaps this will also help you understand my issue a big better.
>http://weblogs.asp.net/hosterposter.../17/443118.aspx
>Thanks for the reply,
>-Carlos
>"Arnie Rowland" wrote:
>|||Sue,
Each user is the dbo to each database.
-Carlos
"Sue Hoegemeier" wrote:
> You mention they should see the databases "since their role
> to each is "DBO".
> The behavior will work as the article describes but I'm not
> sure what you mean by their role to each is dbo. If you mean
> adding the user to db_owner role, that won't do it. The user
> needs to be mapped to dbo - as in be the owner of the
> database. There is a difference.
> Look at who the owner of the database is by executing
> sp_helpdb 'YourDatabase'
> or execute
> sp_helpuser 'dbo'
> -Sue
> On Tue, 26 Sep 2006 13:36:02 -0700, Carlos Caneja
> <CarlosCaneja@.discussions.microsoft.com> wrote:
>
>|||Lines: 1
X-Newsreader: Forte Agent 2.0/32.652
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: c-24-8-112-204.hsd1.co.comcast.net 24.8.112.204
Xref: leafnode.mcse.ms microsoft.public.sqlserver.security:1263
DBO gets misunderstood sometimes so that's why I'm wondering
if that's not the issue. I just did a few tests and it works
fine on two instances I just tested in on. As long as the
logins were database owners, they could see the database.
Anything else, and they could not see the database.
If SomeUser is the login and user in question, the results
for sp_helpuser 'dbo' in that database are:
UserName: dbo
GroupName: db_owner
LoginName: SomeUser
The results for sp_helpdb 'YourDatabase list the owner as
SomeUser.
If that's your results, try changing the database owner
using sp_changedbowner and try changing it back to the user
you need to have own the database.
-Sue
On Wed, 27 Sep 2006 11:01:02 -0700, Carlos Caneja
<CarlosCaneja@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>Each user is the dbo to each database.
>-Carlos
>"Sue Hoegemeier" wrote:
>
I am running into an issue when I run the following query to revoke access
to all the SQL 2005 users from other DB.s
use master
Revoke View any database from public
Go
When the users logs in with SMSE he/she does not see the db under “databas
es”.
They can only access via QUERY.
Thanks,
-CarlosDoesn't it make sense, after all, you removed (REVOKE) the ability to see
(VIEW) any (that means ALL) database to everyone (PUBLIC).
Perhaps you had a different outcome in mind, and you would like some help.
But you're going to have to be more specific, It's a waste of time to try to
guess what you want as a final outcome.
Your question/request is not clear.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Carlos Caneja" <Carlos Caneja@.discussions.microsoft.com> wrote in message
news:A446C59B-C7BA-4CBF-B158-D3F88BD24434@.microsoft.com...
> Hello Everyone,
> I am running into an issue when I run the following query to revoke access
> to all the SQL 2005 users from other DB.s
> use master
> Revoke View any database from public
> Go
> When the users logs in with SMSE he/she does not see the db under
> "databases".
> They can only access via QUERY.
> Thanks,
> -Carlos
>|||Arnie,
Sorry for not being so clear on this. I will try to explaing in a different
way.
We run a shared hosting enviroment and SQL 2005 out of the box let's users
view all the db's and logins on the server when using SMSE.
By revoking the view command the users should still be able to see their
DB's since their role to each is "DBO".
As I said on the post, they can run queriues against the db using SMSE but
the tool will not graphicaly show you the db as it shows Master and tempdb.
Perhaps this will also help you understand my issue a big better.
http://weblogs.asp.net/hosterposter.../17/443118.aspx
Thanks for the reply,
-Carlos
"Arnie Rowland" wrote:
> Doesn't it make sense, after all, you removed (REVOKE) the ability to see
> (VIEW) any (that means ALL) database to everyone (PUBLIC).
> Perhaps you had a different outcome in mind, and you would like some help.
> But you're going to have to be more specific, It's a waste of time to try
to
> guess what you want as a final outcome.
> Your question/request is not clear.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Carlos Caneja" <Carlos Caneja@.discussions.microsoft.com> wrote in message
> news:A446C59B-C7BA-4CBF-B158-D3F88BD24434@.microsoft.com...
>
>|||You mention they should see the databases "since their role
to each is "DBO".
The behavior will work as the article describes but I'm not
sure what you mean by their role to each is dbo. If you mean
adding the user to db_owner role, that won't do it. The user
needs to be mapped to dbo - as in be the owner of the
database. There is a difference.
Look at who the owner of the database is by executing
sp_helpdb 'YourDatabase'
or execute
sp_helpuser 'dbo'
-Sue
On Tue, 26 Sep 2006 13:36:02 -0700, Carlos Caneja
<CarlosCaneja@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Arnie,
>Sorry for not being so clear on this. I will try to explaing in a differen
t
>way.
>We run a shared hosting enviroment and SQL 2005 out of the box let's users
>view all the db's and logins on the server when using SMSE.
>By revoking the view command the users should still be able to see their
>DB's since their role to each is "DBO".
>As I said on the post, they can run queriues against the db using SMSE but
>the tool will not graphicaly show you the db as it shows Master and tempdb.
>Perhaps this will also help you understand my issue a big better.
>http://weblogs.asp.net/hosterposter.../17/443118.aspx
>Thanks for the reply,
>-Carlos
>"Arnie Rowland" wrote:
>|||Sue,
Each user is the dbo to each database.
-Carlos
"Sue Hoegemeier" wrote:
> You mention they should see the databases "since their role
> to each is "DBO".
> The behavior will work as the article describes but I'm not
> sure what you mean by their role to each is dbo. If you mean
> adding the user to db_owner role, that won't do it. The user
> needs to be mapped to dbo - as in be the owner of the
> database. There is a difference.
> Look at who the owner of the database is by executing
> sp_helpdb 'YourDatabase'
> or execute
> sp_helpuser 'dbo'
> -Sue
> On Tue, 26 Sep 2006 13:36:02 -0700, Carlos Caneja
> <CarlosCaneja@.discussions.microsoft.com> wrote:
>
>|||Lines: 1
X-Newsreader: Forte Agent 2.0/32.652
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: c-24-8-112-204.hsd1.co.comcast.net 24.8.112.204
Xref: leafnode.mcse.ms microsoft.public.sqlserver.security:1263
DBO gets misunderstood sometimes so that's why I'm wondering
if that's not the issue. I just did a few tests and it works
fine on two instances I just tested in on. As long as the
logins were database owners, they could see the database.
Anything else, and they could not see the database.
If SomeUser is the login and user in question, the results
for sp_helpuser 'dbo' in that database are:
UserName: dbo
GroupName: db_owner
LoginName: SomeUser
The results for sp_helpdb 'YourDatabase list the owner as
SomeUser.
If that's your results, try changing the database owner
using sp_changedbowner and try changing it back to the user
you need to have own the database.
-Sue
On Wed, 27 Sep 2006 11:01:02 -0700, Carlos Caneja
<CarlosCaneja@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>Each user is the dbo to each database.
>-Carlos
>"Sue Hoegemeier" wrote:
>
Subscribe to:
Posts (Atom)