Re: problem with login list

SkyGringo (doug@xxxxxx) 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, esquel@xxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: Server Migration...
    ... The logins are in the master DB and the DTS packages are in msdb. ... Columnist, SQL Server Professional ... database between server including all Systems Databases, ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Server Logins do not have database access
    ... This posting is provided "AS IS" with no warranties, and confers no rights. ... created all user logins before restoring the databases. ... A few users are SQL Server logins. ...
    (microsoft.public.sqlserver.security)
  • Re: Moving SQL Objects
    ... Moving SQL Server databases to a new location with Detach/Attach ... Transfer Logins and Passwords Between Instances of SQL Server ... How to Resolve Permission Issues When a Database Is Moved Between SQL ...
    (microsoft.public.sqlserver.server)
  • Re: How to I get a list of databases?
    ... But don't you have to have permission to access master.dbo.sysdatabases? ... was able to deduce which databases I can access. ... Depending on the version of SQL Server you are using, ... find any sample ADOX code for getting a list of databases on a server ...
    (microsoft.public.inetserver.asp.db)
  • Re: URGENT (again) :-)
    ... the system databases are special... ... > Using WITH MOVE in a Restore to a New Location with Detach/Attach ... > How To Transfer Logins and Passwords Between SQL Servers ... > Disaster Recovery Articles for SQL Server ...
    (microsoft.public.sqlserver.setup)
Loading