Developer Forum

OK, I obviously dont understand something really simple here. Could someone
help me see the light? :-)

Im logged in as a local machine admin.


C:\> SQLCMD -E
1> grant create database to [valen\nsLocalDBUsers]
2> go
1> grant alter any login to [valen\nsLocalDBUsers]
2> go
Msg 15151, Level 16, State 1, Server VALEN, Line 1
Cannot find the login 'valen\nsLocalDBUsers', because it does not exist or
you d
o not have permission.
1>


Thanks,

Joe

Hello Joe,

I understand that you log into SQL Server 2005 as a local admin but you
could not grant "alter any login" permission to a domain login. If I'm
off-base, please let me know.

I suspect "buitin\Administrators" group might have been removed from the
server or it has been removed "sysadmin" server role.

You may want to login as a domin user or sql login with sysadmin role,
grant yourself "sysadmin" role, and test it again

EXEC sp_helpsrvrolemember 'sysadmin'

EXEC sp_addsrvrolemember 'domain\username', 'sysadmin'

If you have any update, please feel free to let me know. Thank you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://www.yqcomputer.com/ #notif
ications
< http://www.yqcomputer.com/ ;.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
< http://www.yqcomputer.com/ ;.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Hi Peter -

Well, sort of :-). I am allowed to do a "GRANT CREATE DATABASE TO", but
not allowed to do a "GRANT ALTER ANY LOGIN TO".

I have checked, and the builtin\administrator group still exists in SQL
server, and is a member of the sysadmin server role.


Regards,

Joe

Joe ( XXXX@XXXXX.COM ) writes:

CREATE DATABASE is a database permission, ALTER ANY LOGIN is a server-
level permission. Database permissions are granted to database principals,
server permissions to logins.

It's certainly interesting that [valen\nsLocalDBUsers] is a database
user, but not a login, particularly if this is the master database.
But I would dig into sys.server_principals and sys.database_principals
to see what anomalies I could find.



--
Erland Sommarskog, SQL Server MVP, XXXX@XXXXX.COM

Books Online for SQL Server 2005 at
http://www.yqcomputer.com/
Books Online for SQL Server 2000 at
http://www.yqcomputer.com/

Ah, I think I might see the problem now based on your response.
valen\nsLocalDBUsers is a group, not a user. It is a Windows group that I
added to the database in question using "sp_grantdbaccess". My guess is
that I did not first add it to the server because sp_grantdbaccess seems to
do the grantlogin at the server level "automatically".

Am I correct that I should have added the group to the server first, and
then granted access to the database? What is the recommended method for
adding a windows user/group to the server?

Thanks for any additional help, I am off to read the online help to
understand this better :-)

joe

With your help, I figured it out. What happened was this. I did a
sp_grantdbaccess [valen\nslocaldbusers] without ever doing the CREATE LOGIN.
The sp_grantdbaccess seems to automatically add the group to db principals,
but not to server principals. Once I did the CREATE LOGIN, the GRANT ALTER
ANY LOGIN worked for that account.

Thank you for pointing me in the right direction.

Joe