GRANT CREATE DATABASE versus GRANT ALTER ANY LOGIN

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,
JoeHello 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://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
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://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
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
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:6h$5$PNfHHA.4692@.TK2MSFTNGHUB02.phx.gbl...
> 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
> l]
> ications
> <[url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx" target="_blank">http://msdn.microsoft.com/subscript...ps/default.aspx>.
> 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://msdn.microsoft.com/subscript...rt/default.aspx>.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>|||Joe (jwdaigle@.nospam.nospam) writes:
> 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>
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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9911AB07D649Yazorman@.127.0.0.1...
> Joe (jwdaigle@.nospam.nospam) 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, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
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|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9911AB07D649Yazorman@.127.0.0.1...
> Joe (jwdaigle@.nospam.nospam) 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, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
With your help, I figured it out. What happened was this. I did a
sp_grantdbaccess [valen\nslocaldbusers] without ever doing the CREATE LO
GIN.
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