Smo Scripter scripting user script with "Incorrect Syntax"

Question

I am using Smo to script a db's users and their roles. Here is how I am doing it:

var scripter = new Scripter(server)
{
    Options = new ScriptingOptions()
    {
        IncludeIfNotExists = true,
        IncludeDatabaseRoleMemberships = true,
        // lots of other options here
    }
};

foreach (User smoObject in database.Users)
{
    var sc = scripter.Script(new Urn[] { smoObject.Urn });
    // write to file here
}

The result is something like this:

/****** Object:  User [myuser]    Script Date: 12/10/2013 5:00:57 PM ******/
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'myuser')
CREATE USER [myuser] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]
sys.sp_addrolemember @rolename = N'r_execprocs', @membername = N'myuser'
sys.sp_addrolemember @rolename = N'db_owner', @membername = N'myuser'
sys.sp_addrolemember @rolename = N'db_datareader', @membername = N'myuser'
sys.sp_addrolemember @rolename = N'db_datawriter', @membername = N'myuser'

But if you were to try and run this script against the database you would get:

"Msg 102, Level 15, State 1, Line 4 Incorrect syntax near 'sys'."

If, however, I manually change the script to be:

/****** Object:  User [myuser]    Script Date: 12/10/2013 5:00:57 PM ******/
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'myuser')
CREATE USER [myuser] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]
GO
sys.sp_addrolemember @rolename = N'r_execprocs', @membername = N'myuser'
GO
sys.sp_addrolemember @rolename = N'db_owner', @membername = N'myuser'
GO
sys.sp_addrolemember @rolename = N'db_datareader', @membername = N'myuser'
GO
sys.sp_addrolemember @rolename = N'db_datawriter', @membername = N'myuser'
GO

The script works perfectly. Is there a way to either:

run the script as is without getting "Incorrect Syntax" or
make the scripter generate this script in a way that is runnable ?

Stored procedure calls that aren't the first command in a batch need to be preceded by EXEC. You can either add EXEC before each call to sys.sp_addrolememeber, or put each command into its own batch by using GO like you've shown here. — Michael Fredrickson
– Michael Fredrickson, Commented Dec 10, 2013 at 23:36
Is there a way to have that generated using the Smo library? — saml
– saml, Commented Dec 11, 2013 at 13:32

saml · Accepted Answer · 2013-12-11 14:44:58Z

Well, figured it out. The scripter can do any combination of 2 things:

output the script to an in memory StringCollection
output the script to a file determined by scripter.Options.FileName

When scripting to a string collection, for some reason it does not script batch terminator GO between sp calls, however it does when scripting to a file. So I changed the above code to look like this:

var scripter = new Scripter(server)
{
    Options = new ScriptingOptions()
    {
        IncludeIfNotExists = true,
        IncludeDatabaseRoleMemberships = true,

        //ADDED THIS OPTION
        FileName = Path.Combine(path, "Script.sql"),

        // lots of other options here
    }
};

foreach (User smoObject in database.Users)
{
    var sc = scripter.Script(new Urn[] { smoObject.Urn });
    // no need to write to file anymore since scripter automatically does it
}

The result was:

/****** Object:  User [myuser]    Script Date: 12/10/2013 5:00:57 PM ******/
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'myuser')
CREATE USER [myuser] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]
GO
sys.sp_addrolemember @rolename = N'r_execprocs', @membername = N'myuser'
GO
sys.sp_addrolemember @rolename = N'db_owner', @membername = N'myuser'
GO
sys.sp_addrolemember @rolename = N'db_datareader', @membername = N'myuser'
GO
sys.sp_addrolemember @rolename = N'db_datawriter', @membername = N'myuser'
GO

Collectives™ on Stack Overflow

Smo Scripter scripting user script with "Incorrect Syntax"

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related