search

Buffalohuntington / sql-dbdiff

Automatically exported from code.google.com/p/sql-dbdiff
0 stars 0 forks source link

Role does not have password #37

Open GoogleCodeExporter opened 8 years ago

GoogleCodeExporter commented 8 years ago 
What steps will reproduce the problem?
1. Add a role.
2.Compare DBs

What is the expected output? What do you see instead?

According to BOL, "CREATE ROLE" does not have "WITH PASSWORD" option and 
"CREATE ROLE xxx WITH PASSWORD" gives an error:
Incorrect syntax near the keyword 'with'. If this statement is a common table 
expression or an xmlnamespaces clause, the previous statement must be 
terminated with a semicolon.

Patch:
Index: DBDiff.Schema.SQLServer2005/Model/Role.cs
===================================================================
--- DBDiff.Schema.SQLServer2005/Model/Role.cs   (revision 37)
+++ DBDiff.Schema.SQLServer2005/Model/Role.cs   (working copy)
@@ -43,7 +43,6 @@
             string sql = "";
             sql += "CREATE " + ((type == RoleTypeEnum.ApplicationRole)?"APPLICATION":"") + " ROLE ";
             sql += FullName + " ";
-            sql += "WITH PASSWORD = N'" + password + "'";
             if (!String.IsNullOrEmpty(Owner))
                 sql += " ,DEFAULT_SCHEMA=[" + Owner + "]";
             return sql.Trim() + "\r\nGO\r\n";

What version of the product are you using? On what operating system?
Revision 37; windows XP;

Please provide any additional information below.
SQL Server 2005.

Original issue reported on code.google.com by kot.bege...@gmail.com on 13 Sep 2010 at 9:48

GoogleCodeExporter commented 8 years ago 
Ok, I figured it out that Application role has a password, so valid fix is this:
Index: DBDiff.Schema.SQLServer2005/Model/Role.cs
===================================================================
--- DBDiff.Schema.SQLServer2005/Model/Role.cs   (revision 37)
+++ DBDiff.Schema.SQLServer2005/Model/Role.cs   (working copy)
@@ -43,7 +43,8 @@
             string sql = "";
             sql += "CREATE " + ((type == RoleTypeEnum.ApplicationRole)?"APPLICATION":"") + " ROLE ";
             sql += FullName + " ";
-            sql += "WITH PASSWORD = N'" + password + "'";
+            if(!string.IsNullOrEmpty(password))
+                sql += "WITH PASSWORD = N'" + password + "'";
             if (!String.IsNullOrEmpty(Owner))
                 sql += " ,DEFAULT_SCHEMA=[" + Owner + "]";
             return sql.Trim() + "\r\nGO\r\n";

Original comment by kot.bege...@gmail.com on 13 Sep 2010 at 9:53