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

GoDaddy SQL Web Admin -- Importing CSV file

Okay -- Frustrated... any guideance appreciated.
Our website is currently using an MS-ACCESS file for it's back end
database. Hosted on GoDaddy. Due to performance issues, we are
switching over to GoDaddy's MS-SQL Server. Our only management tool is
the SQL WEB ADMIN.
The only way we can see to import data into a table is through
importing a csv. THIS IS FINE!
We went to access, and exported the tables, using a comma delimited,
text identiffier of quotes, and imported it into sql through the SQL
WEB ADMIN.
EXCEPT -- all the text fields physically kept the quotes.
A field that previously contained: Hello kitty, how are you
Now contains "Hello kitty, how are you"
We have several long descriptions and text fields that contain commas
and semi-colons so we definately need a text identifier. So how do we
export the file from access and successfully import it into our godaddy
hosted SQL server using ms SQL WEB ADMIN?
Any help, guidance, comments would be greatly appreciated.
Don O
Palm Springs, CA
president@.palmspringsbears.org
You can use DTS package to migrate data from MS ACCESS to direct SQL Server
without exporting to csv.
-MB
"BigDesert" <doldenburg@.bigdesert.net> wrote in message
news:1167600961.063396.234590@.n51g2000cwc.googlegr oups.com...
> Okay -- Frustrated... any guideance appreciated.
> Our website is currently using an MS-ACCESS file for it's back end
> database. Hosted on GoDaddy. Due to performance issues, we are
> switching over to GoDaddy's MS-SQL Server. Our only management tool is
> the SQL WEB ADMIN.
> The only way we can see to import data into a table is through
> importing a csv. THIS IS FINE!
> We went to access, and exported the tables, using a comma delimited,
> text identiffier of quotes, and imported it into sql through the SQL
> WEB ADMIN.
> EXCEPT -- all the text fields physically kept the quotes.
> A field that previously contained: Hello kitty, how are you
> Now contains "Hello kitty, how are you"
> We have several long descriptions and text fields that contain commas
> and semi-colons so we definately need a text identifier. So how do we
> export the file from access and successfully import it into our godaddy
> hosted SQL server using ms SQL WEB ADMIN?
> Any help, guidance, comments would be greatly appreciated.
> Don O
> Palm Springs, CA
> president@.palmspringsbears.org
>
|||I don't use sql web admin so can't really help much there.
However, if you have access to a sql client tool like Query Analyzer, you
can read data from Access (via opendatasource or openrowset) and insert it
directly into sqlserver table.
e.g.
INSERT into SqlTable
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="x:\godaddy\myAccess.mdb";User
ID=Admin;Password=;')...myAccessTable
INSERT into SqlTable
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
x:\godaddy\myAccess.xls.mdb';'admin';'', myAccessTable) as x
-oj
"BigDesert" <doldenburg@.bigdesert.net> wrote in message
news:1167600961.063396.234590@.n51g2000cwc.googlegr oups.com...
> Okay -- Frustrated... any guideance appreciated.
> Our website is currently using an MS-ACCESS file for it's back end
> database. Hosted on GoDaddy. Due to performance issues, we are
> switching over to GoDaddy's MS-SQL Server. Our only management tool is
> the SQL WEB ADMIN.
> The only way we can see to import data into a table is through
> importing a csv. THIS IS FINE!
> We went to access, and exported the tables, using a comma delimited,
> text identiffier of quotes, and imported it into sql through the SQL
> WEB ADMIN.
> EXCEPT -- all the text fields physically kept the quotes.
> A field that previously contained: Hello kitty, how are you
> Now contains "Hello kitty, how are you"
> We have several long descriptions and text fields that contain commas
> and semi-colons so we definately need a text identifier. So how do we
> export the file from access and successfully import it into our godaddy
> hosted SQL server using ms SQL WEB ADMIN?
> Any help, guidance, comments would be greatly appreciated.
> Don O
> Palm Springs, CA
> president@.palmspringsbears.org
>
|||Don O,
I just got off the phone with GoDaddy. They are telling me that what you are trying to do won't work on their servers~ I am trying to do the same thing.
Have you gotten yours to work yet?
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com
|||Frankly, I'm not surprised. What you folks want to do amounts to sitting
at the keyboard of their server and having at it...
Regards,
Hank Arnold
Angie D wrote:
> Don O,
> I just got off the phone with GoDaddy. They are telling me that what you are trying to do won't work on their servers~ I am trying to do the same thing.
> Have you gotten yours to work yet?
>
> EggHeadCafe.com - .NET Developer Portal of Choice
> http://www.eggheadcafe.com

Global temporary table and SP

Hello all,

I'm using SS2K on W2K.

Brieffing: Many months ago, I created a stored procedure only used by those
with admin rights in SS. Now, someone else (without admin rights) has to run
it. I gave him rigth to execute the SP but, at the second and more
execution, he got a error message concerning a temp table already existing
(see further).

The SP:
----------------
CREATE PROCEDURE MySP
@.Type INT

DECLARE @.strSQL AS VARCHAR(4000)

IF EXISTS (SELECT table_name FROM tempdb.information_Schema.tables WHERE
table_name = '##MyTmpTable')
DROP TABLE ##MyTmpTable

SELECT @.strSQL = 'SELECT MyField1, MyField2, MyField3 INTO ##MyTmpTable FROM
MyTable'

EXECUTE(@.strSQL)

IF @.Type = 1
SELECT MyField1
FROM ##MyTmpTable
ELSE IF @.Type = 2
SELECT MyField2
FROM ##MyTmpTable
ELSE
SELECT MyField3
FROM ##MyTmpTable
GO
----------------

The error I got on the second time the user run the sp is: "Table
##MyTmpTable already exists." The front-end where this SP is run is A97.
That's where I got this message. This SP looks like a simple SELECT query
from A97 users perspective.

Please, do no argue about the way of doing the work done! It is simplified
at most in order to make it short and easy to read. I have to use the
command "EXECUTE(String)" and, because of this, I connot use a local
temporary table instead of a global one.

I suspect non-admin user cannot drop global temporary table, but the error
message makes me believe that this code line is not even run, as if the
condition "IF EXISTS(...)" return false even if the table actualy exists.

Anybody can help about this? What should I do to solve this problem?

YannickIf two people attempt to execute that procedure at the same time they
will get a conflict because it already exists. You could wrap the whole
thing in an execute statement and change the global temporary table to
a local (using a single #).

Also, try creating the temporary table first and inserting data into it
rather than creating it on the fly. You reduce the amount of locks on
"MyTable".|||It solved the problem. Many thanks.

Yannick

> Also, try creating the temporary table first and inserting data into it
> rather than creating it on the fly. You reduce the amount of locks on
> "MyTable".