March 4, 2011 at 4:39 pm
Hello -
I am attempting to string together a series of .SQL scripts for the creation of a new production database. The issue that I am having in doing this is that the batch file is calling SQLCMD with a series of switches that should allow for it to process without error, but of course - I am getting some.
Here is the contents of my batch file...
SQLCMD -E -i"c:\Documents and Settings\ryarger\Desktop\'database name'\Database\BETA - March 2011\'database name' dbs\Cloud.sql"
PAUSE
The database name has been removed and replaced with 'database name'. In other words - that's not what is actually in the path.
Now - you may notice that with the -E switch, there no longer is a login specified, but I have been using one all this time (just tried it one last time before posting out here, without it as part of the switch), and it gives the same error results. I've also used the -U with -P, to try and get the 'sa' login to work, but I am still getting errors. The error I am getting is related to an attempt to - apparently - GRANT permissions that would conflict with the 'sa' account (along with a list of others that it displays...dbo, myself, etc...).
Here is that error...
Creating and Granting permissions to user 'NotifyUser'...
Creating and Granting permissions to user 'EmailUser'...
(1 rows affected)
(1 rows affected)
(1 rows affected)
... <---- have excluded some of the results for the sake of space.
---------------------------------------
Starting execution of InstallCommon.SQL
---------------------------------------
Creating the aspnetdb database...
Changed database context to 'aspnetdb'.
(120 rows affected)
Creating the aspnet_Applications table...
Creating the aspnet_Users table...
Creating the aspnet_SchemaVersions table...
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_
schema, sys, or yourself.
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_
schema, sys, or yourself.
(0 rows affected)
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_
schema, sys, or yourself.
Creating the vw_aspnet_Applications view...
Creating the vw_aspnet_Users view...
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_
schema, sys, or yourself.
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_
schema, sys, or yourself.
----------------------------------------
Completed execution of InstallCommon.SQL
----------------------------------------
-------------------------------------------
Starting execution of InstallMembership.SQL
-------------------------------------------
Changed database context to 'aspnetdb'.
Creating the aspnet_Membership table...
Creating the vw_aspnet_MembershipUsers view...
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_
schema, sys, or yourself.
(0 rows affected)
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_
schema, sys, or yourself.
--------------------------------------------
Completed execution of InstallMembership.SQL
--------------------------------------------
--------------------------------------
Starting execution of InstallRoles.SQL
--------------------------------------
Changed database context to 'aspnetdb'.
Creating the aspnet_Roles table...
Creating the aspnet_UsersInRoles table...
Creating the vw_aspnet_Roles view...
Creating the vw_aspnet_UsersInRoles view...
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_
schema, sys, or yourself.
(0 rows affected)
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_
schema, sys, or yourself.
---------------------------------------
Completed execution of InstallRoles.SQL
---------------------------------------
Changed database context to 'database name'.
Msg 1934, Level 16, State 1, Server BMULLIN_WKSTN, Line 1
INSERT failed because the following SET options have incorrect settings: 'QUOTED
_IDENTIFIER'. Verify that SET options are correct for use with indexed views and
/or indexes on computed columns and/or query notifications and/or xml data type
methods.
Msg 1934, Level 16, State 1, Server BMULLIN_WKSTN, Line 3
INSERT failed because the following SET options have incorrect settings: 'QUOTED
_IDENTIFIER'. Verify that SET options are correct for use with indexed views and
/or indexes on computed columns and/or query notifications and/or xml data type
methods.
Msg 1934, Level 16, State 1, Server BMULLIN_WKSTN, Line 5
INSERT failed because the following SET options have incorrect settings: 'QUOTED
_IDENTIFIER'. Verify that SET options are correct for use with indexed views and
/or indexes on computed columns and/or query notifications and/or xml data type
methods.
Msg 1934, Level 16, State 1, Server BMULLIN_WKSTN, Line 7
INSERT failed because the following SET options have incorrect settings: 'QUOTED
_IDENTIFIER'. Verify that SET options are correct for use with indexed views and
/or indexes on computed columns and/or query notifications and/or xml data type
methods.
Msg 1934, Level 16, State 1, Server BMULLIN_WKSTN, Line 9
INSERT failed because the following SET options have incorrect settings: 'QUOTED
_IDENTIFIER'. Verify that SET options are correct for use with indexed views and
/or indexes on computed columns and/or query notifications and/or xml data type
methods.
Msg 1934, Level 16, State 1, Server BMULLIN_WKSTN, Line 11
INSERT failed because the following SET options have incorrect settings: 'QUOTED
_IDENTIFIER'. Verify that SET options are correct for use with indexed views and
/or indexes on computed columns and/or query notifications and/or xml data type
methods.
C:\Documents and Settings\ryarger\Desktop\OnDemand\Database\BETA - March 2011>PA
USE
Press any key to continue . . .
So if you look above, what you are seeing is the script in question (a generic one that Microsoft provides for those needing to do ASP .NET programming with databases) is a series of errors, all based on "Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_
schema, sys, or yourself", yet I have no issues running this script outside of SQLCMD and the batch file. The subsequent errors that follow - I assume - are due to the initial error in it trying to GRANT.
Any insight or ideas would be most appreciated!
Thank you in advance!
March 8, 2011 at 2:47 pm
Perhaps this will be a little more clear. I am basically trying to get the following SQLCMD to work...
SQLCMD -E -i"c:\Documents and Settings\ryarger\Desktop\OnDemand\Database\BETA - March 2011\OnDemand dbs\Cloud.sql"
PAUSE
Here is the contents of the Cloud.sql file towards the bottom of the first db's creation, that is attempting to call the other files and their contents...
:On Error exit
:r "C:\Documents and Settings\ryarger\Desktop\OnDemand\Database\BETA - March 2011\Third Party dbs\aspnetscripts.sql"
:r "C:\Documents and Settings\ryarger\Desktop\OnDemand\Database\BETA - March 2011\db Test Data\OnDemand Test-Data.sql"
:r "C:\Documents and Settings\ryarger\Desktop\OnDemand\Database\BETA - March 2011\Update.sql"
PRINT N'Creation of OnDemand Cloud Database system is complete.'
GO
The batch file that is being launched from Windows AND/OR launched from the CMD line (I've tried both with the same error results) is giving me the following error, multiple times, when it attempts to start the second db's creation (aspnetdb).
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_
schema, sys, or yourself.
And then the other errors I posted initially show up towards the bottom before it fails all together.
If anyone can tell me if my permissions settings/switches are the issue with the SQLCMD line I have constructed, or if something else is wrong here, I certainly would appreciate it.
March 9, 2011 at 8:25 am
Just a final note to close this one out - I was able to find something in a 4 year old post that helped me get this resolved. When running the SQLCMD, if your script is going to CREATE VIEWs during the process, it helps to have these set prior to doing a CREATE VIEW. I placed the following prior to the creation of each view in the aspnetdb from Microsoft, and the only errors I get now are the benign ones about not being able to GRANT, DENY or REVOKE from my account (which makes sense now that I look at it - I'm running the script, so of course they shouldn't be changed).
Here are the settings...
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_WARNINGS ON
GO
SET ANSI_PADDING ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply