CODEWITHSUNDEEP
c#sqlsmo
saml
saml

Reputation: 463

Smo Scripter scripting user script with "Incorrect Syntax"

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:

Upvotes: 1

Views: 693

Answers (1)

saml
saml

Reputation: 463

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

Upvotes: 1

Related Questions