473,324 Members | 2,178 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

db_owners unable to see login list

Hi. We are using SQL Server 2005 64-bit on Windows Server 2003 R2 x64
Enterprise Edition. I have a really weird problem where I have people
out there who are placed in the db_owner, db_accessadmin, and
db_securityadmin roles in their database, and yet they are unable to
add new users to their databases, despite the fact that logins exist
for these users on the server. The reason is that when attempting to
add a new user in Management Studio, upon using the "Browse" option to
look at the logins, the complete list is not presented to them. In
fact, they only see a couple of logins or so (the server has hundreds
of logins). Also, if they try to type in the login name directly,
they get a "permission denied" error message. Since they cannot add
users to their databases, they have to call me and have me do it for
them.

I created a test, non-privileged, SQL-authenticated login to test
things for my own sake, and when logged in under the test account, I
saw the same behavior. I had db_owner role for a single test
database, and when I went to add users, the only server logins that
were displayed were my own (the test login in this case) and that of
sa. Further, when I queried the master.sys.syslogins view, I saw the
same thing, it only came back with the rows for myself and sa.

It appears that this is clearly a security problem, but I'm not sure
at what level to look. We never had this problem with SQL Server 2000
or earlier. Does anyone have any idea what I can do to allow my
db_owners to see all the server logins, and therefore be able to add
users to their databases as they see fit? I really do NOT want to
assign them to server-level security roles, for obvious reasons.

Thanks,
SkyGringo

Jul 18 '07 #1
1 3675
[posted and mailed]

You asked the same question yesterday. For your convenience, I repeat
my answer, and send a mail copy as well. Please reply in the newsgroup.

SkyGringo (do**@bu.edu) writes:
I'm using the 64-bit version of SQL Server 2005, SP2, on Windows
Server 2003 R2 X64 Enterprise Edition. I've got a bunch of users out
there who are the db_owner, db_accessadmin and db_securityadmin of
their different respective databases. I would expect that they would
be able to add users to their databases, given that a login exists on
the server. However, when they go to browse logins to add a user in
Management Studio, they are only shown a very short list (like,
themselves and sa, and that's it). We have hundreds of logins on the
server, and they should be able to add any one of them to their
databases if they wish. And if they try to type in the login name
directly, they get a permission denied error.
They need to have VIEW DEFINITION on the logins they need to add. There
is no permission VIEW ANY LOGIN, but there is a server-level VIEW ANY
DEFINITION you can grant to them, but I would think twice before you
did.
The only other thing I can add is it's not just occurring with the GUI
interface; the same thing happens when I do a direct query on the
master.sys.syslogins view: I only see the same two logins. So it
appears it's happening at that level and the result appears up in the
GUI.
It appears that you are of the old SQL 2000 school. :-)

Microsoft did a lot around security in SQL 2005, and one thing is
that objects are no longer visible to everyone. Essentially, you
can only see an object, if you have permission to it.

And the place to look for logins these days, is sys.server_principals.
While the old system tables are around for compatibility, they may not
show aspects that are new to SQL 2005.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jul 18 '07 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: allenj | last post by:
DB2 UDB 7.2 WSE Fixpak 9 Linux Red Hat 7.3 A very strange thing just happened w/ my DB2 server. I am on Linux and using NIS - the instance owner is in the servers /etc/passwd file. Suddenly,...
1
by: Brian Pittman | last post by:
Hi all, As the subject says I'm having trouble connecting to SQL Server 2K via Visual Studio.NET 2002 (VB). It tells me that SQL Server does not exist or access is denied. I know that the...
0
by: Dilip | last post by:
hi to all . i am fresher in ASP.NET and i had done almost about my project. but now i am unable to deal with admin login . i am having a table named "login" and 3 fields "username","password...
0
by: muder | last post by:
I have a standard Login ASP.NET 2.0 control on a login Page, a LoginName and LoginStatus controls on the member's page. once the user login successfully I am redirecting the user to Member.aspx...
0
by: Buddy Home | last post by:
Hello, I'm trying to upload a file programatically and occasionally I get the following error message. Unable to write data to the transport connection: An established connection was aborted...
3
by: Buddy Home | last post by:
Hello, I'm trying to upload a file programatically and occasionally I get the following error message. Unable to write data to the transport connection: An established connection was aborted...
6
by: Varlamov Konstantyn | last post by:
I have simple script: <?php $connection = ssh2_connect("ip", 22); ssh2_auth_password($connection,"login","test");
3
by: aydeejay | last post by:
I'm trying to troubleshoot an issue where users are not able to bind with LDAP via "GetObject" through our ASP Classic Intranet if they stay logged in overnight (beyond their allowed login hours). ...
3
by: srikanthrsb | last post by:
Hi, Currently we are facing a unique issue w.r.t Motorola Devices.We are having a WAP Email application,which we are able to login from Openwave and other browsers in various devices like...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.